Date: Wed, 24 May 2006 11:16:52 -0700
Reply-To: "William W. Viergever" <william@VIERGEVER.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "William W. Viergever" <william@VIERGEVER.NET>
Subject: Re: SAS DAte problem
In-Reply-To: <006d01c44180$2b4e7d40$3064ea3c@deanl7zxt4dwzp>
Content-Type: text/plain; charset="us-ascii"; format=flowed
hi dean:
yes you have a date problem
yuoor PC is showing its 2004
:-)
At 04:12 AM 5/24/2004, Dean Edwards wrote:
>I am a newbie here but have found the SAS forum very useful and got
>a lot of excellent ideals that have helped me automate my SAS jobs.
>I have inherited some SAS programs from my predecessors that were
>created by many different people and I have been trying to
>streamline some of them. One of out top property investor's Robert
>Jones a self made multi-millionaire who has written many books has
>said, "Employee's should do less work and more thinking." He wasn't
>being facetious or cheeky.
>
>
>
>Some of the "brilliant thinking" ideals I have applied already that
>I got from the forum which has greatly reduced my workload and the
>tedious manual labour
>
>intensive tasks of editing a lot of files etc using things likes
>%include, sleep wake function, SAS/Excel macro etc. Below is
>something that I am trying to automate. It has to be run every month
>for trade output. The key is to manually edit and change the months.
>The latest head.release_date_code = month being exactly as written
>in quote marks as '200505'.
>
>
>
>
>I have a SAS program and have to edit the month date for the
>lastest trade month based on 24 months dataa and want to get a
>rolling 24 months as it is run monthly. Previous month would have
>been '200304 to 200603. the new date should be
>
>
>
>Start Month '200305' to ending month '200604' Should look like this below
>
>
> 200305
>
> 200306
>
> 200307
>
> 200308
>
> 200309
>
> 200310
>
> 200311
>
> 200312
>
> 200401
>
> 200402
>
> 200403
>
> 200404
>
> 200405
>
> 200406
>
> 200407
>
> 200408
>
> 200409
>
> 200410
>
> 200411
>
> 200412
>
> 200501
>
> 200502
>
> 200503
>
> 200504
>
>
>
>
>
>
>
>
>options SYMBOLGEN mprint;
>
>
>
>data _null_ ;
>
>today = today();
>
>begmonth = intnx('month',today,-23);
>
>Endmonth = intnx('month',today,-1);
>
>call symput('starting_month',"'" || put(begmonth, yymmn6.)|| "'");
>
>call symput('ending_month',"'" || put(endmonth, yymmn6.)|| "'");
>
>%let start_year=&starting_month;
>
>%let end_year=&ending_month;
>
>%put my first macro variable is &start_year;
>
>%put my second macro variable is &end_year;
>
>
>
>run;
>
>
>
>
>
>I have achieve the aim and it is inserted in the program below
>using the today function.
>
>where (head.release_date_code >= '200305' and
> head.release_date_code <= '200604')
>
>where (head.release_date_code >= &start_year and
> head.release_date_code <= &end_year)
>
>
>
>
>
>
>
>
>
>
>rsubmit;
> %libname (save, J:\P_Subscr\prod\sas\output\sasuser);
>
>
>options SYMBOLGEN mprint;
>
>
>
>data _null_ ;
>
>today = today();
>
>begmonth = intnx('month',today,-23);
>
>Endmonth = intnx('month',today,-1);
>
>call symput('starting_month',"'" || put(begmonth, yymmn6.)|| "'");
>
>call symput('ending_month',"'" || put(endmonth, yymmn6.)|| "'");
>
>%let start_year=&starting_month;
>
>%let end_year=&ending_month;
>
>%put my first macro variable is &start_year;
>
>%put my second macro variable is &end_year;
>
>
>
>run;
>
>
>
>PROC SQL;
> %contosyb (server=syb03,database=overseas_trade_out_prod);
> create table save.exports as
> SELECT * FROM CONNECTION TO SYBASE
> (SELECT
>
> head.release_date_code as rel_d,
> head.transport_mode_code as trans_c,
> head.load_port_code as nz_pt_c,
> head.dest_country_code as cod_c,
> head.flight_or_craft_text as fl_shp_t,
> sum(line.apportioned_gross_wgt_nbr) as app_gwt
>
> from export_line_op line, trade_header_op head
> where (head.release_date_code >= &start_year and
> head.release_date_code <= &end_year)
> and head.transport_mode_code = '4'
> and head.entry_nbr = line.entry_nbr
> and line.trade_type_code in ('c', 'b')
> group by head.release_date_code,
> head.dest_country_code,
> head.transport_mode_code,
> head.load_port_code,
> head.flight_or_craft_text);
>
>disconnect from sybase;
>quit;
>
>
>proc freq;
> tables rel_d;
> tables trans_c;
> tables nz_pt_c;
> tables fl_shp_t;
> tables cod_c;
>run;
>
>
>proc print data=save.exports (obs=100);
>run;
>
>endrsubmit;
>
>
>
>My problem is that I have another program below that has a fix date
>and below is the code that follows. Is there anyway to fix the start
>date for 200307 and then roll it forward so thAT i DON'T HAVE TO
>EDIT THE sas PROGRAM TO CHANGE THE DATES in Proc SQL AnY IDEALS?
>THIS IS THE SECOND PROGRAM BELOW that does it.
>
>i
>
>**************************************************************************;
>* Programme Name:-Exports Cargo Data ;
>* Extract data from sybase, needed for tables ;
>* IMPORTANT NOTE FOR RUNNING JOB: ;
>* Start Date must always begin in July as this is a July Year to Date job ;
>* e.g 200307-200506 would be the next period (24 months data). ;
>* The new start period after that would roll over to 200407-200507 ;
>* then 200407-200508, 200407-200509, 200407-200510 etc until reaching the ;
>* final period 200407-200606 (24 months) then roll over to ;
>* 200507 as the new July start period. ;
>* End date must always be updated for the new monthly data. ;
>**************************************************************************;
>
>
>
>***************************************;
> * Standard program structure follows:- ;
> ***************************************;
> Options nocenter symbolgen missing = 0 ;
>
>
>*---------------------------------------------------------------------------;
> * Get parameters from Users h:\filetab\Sas_prod.txt. This is
> a ;
> * date file and you eidt the month e.g 200504 and create macro
> variables. ;
>
>*---------------------------------------------------------------------------;
>
>
>
> DATA parms;
> length server dbase end_mth path $43.;
> infile "h:\filetab\Sas_prod.txt" delimiter = '#' ;
> input @1 server $ dbase $ end_mth $ path $;
> run;
>
> Data parms;
> set parms;
>
> call symput ('server', trim(server));
> call symput ('dbase', trim(dbase));
> call symput ('end_mth', put(end_mth, $6.));
> call symput ('path', trim(path));
>
> last_mth = substr(end_mth,5,2);
> call symput ('last_mth', put(last_mth, $2.));
>
> ** need to add py_mth, which is same month , previous year** ;
>
> year = substr(end_mth, 1,4) ;
> py_mth = put(input(year,4.) - 1,4.)||last_mth;
>
> call symput ('py_mth', py_mth);
>
> ** Derive start of previous June year **;
>
> if last_mth > '06'
> then
> first_July_mth = put(input(year,4.) - 1,4.)||'07';
> else
> first_July_mth = put(input(year,4.) - 2,4.)||'07';
>
> call symput ('first_July_mth', first_July_mth);
>
> ** Derive macro var for start of June year;
>
> if last_mth > '06'
> then
> beg_ytd = substr(end_mth,1,4)||'07';
> else
> do;
> year = substr(end_mth,1,4) ;
> beg_ytd = put(input(year,4.) - 1,4.)||'07';
> end;
> call symput ('beg_ytd', put(beg_ytd, $6.));
>
> ** macros for year1, year2, mth1 and mth2 ** ;
> call symput('year1', substr(py_mth,1,4)) ;
> call symput('year2', substr(end_mth,1,4)) ;
> call symput('mth1', substr(py_mth,5,2)) ;
> call symput('mth2', substr(end_mth,5,2)) ;
>
>
>
>
> put _all_ ;
>
> run ;
---------------------------------------------------
William W. Viergever
Viergever & Associates
Health Data Analysis / Systems Design & Development
Sacramento, CA 95825
william@viergever.net
(916) 483-8398
---------------------------------------------------
| 
