DATA Step, Macro, Functions and more

Concatenate datasets as it get created

Reply
Occasional Contributor
Posts: 5

Concatenate datasets as it get created

[ Edited ]

I have dataset getting created every 6months.

 

s_200806

s_200812

s_200906

s_200912

 

Now if i run for period 201706, it will create s_201706

 

So my start date is 200806

 

%let start =200806;

%let end=201006

 

i want to cancatenate all the dataset from start date to end into new dataset.

 

data new;

set s_200806 s_200812 s_200906 s_200912 s_200906 s_200912 s_201006 ;

run;

 

So if i run for period 201012, again i have to write the code.

 

data new;

set s_200806 s_200812 s_200906 s_200912 s_200906 s_200912 s_201006 s_201012 ;

run;

 

What i am looking is just create macro loop to concatenate every six month dataset just changing start and end macro.

PROC Star
Posts: 1,190

Re: Concatenate datasets as it get created

Posted in reply to geetha1a2b3c

Use PROC APPEND

 

proc append base=sample.new_sum data=new_201503;
run;
Occasional Contributor
Posts: 5

Re: Concatenate datasets as it get created

For example if run the code based on period.

For example
%let date =201701;

i want to create new dataset sample.new_sum and this dataset should contain all the previous dates dataset, as 201701 dataset is newly created
Frequent Contributor
Posts: 142

Re: Concatenate datasets as it get created

Posted in reply to geetha1a2b3c

Try this.

 

%macro all(dt,dt1);

 

data new_&dt1.;

set sample.new_&dt.;

 

run;

proc append base=sample.new_sum data= new._&dt1. force;

 

run;

%mend;

%all(201501,201502);

%all(201502,201503);

 

Respected Advisor
Posts: 4,540

Re: Concatenate datasets as it get created

Posted in reply to geetha1a2b3c

@geetha1a2b3c

If the main purpose for combining all your monthly slices is to easier address the data in your code and your data sets follow a naming pattern then you could also use a wildcard in the name and though avoid data replication.

 

Code like below will read all data sets in library sample with a name of new_<anything>

data want;
  set sample.new_: ;
run;
Respected Advisor
Posts: 4,540

Re: Concatenate datasets as it get created

Posted in reply to geetha1a2b3c

@geetha1a2b3c

You don't need a macro loop. Just query the dictionary tables to retrieve all table names which follow a certain naming pattern, store the result in a macro variable and then use this macro variable in your data step.

Below sample code illustrating how that's often done.

data 
  s_200712
  s_200806
  s_200812
  s_200906
  s_200912
  ;
  set sashelp.class;
  output;
run;

%let start =200806;
%let end=201006;

%let dslist=;
proc sql noprint;
  select cats(libname,'.',memname) into :dslist separated by ' '
  from dictionary.tables
  where libname='WORK' and memname like 'S^_%' escape '^'
    and input(scan(memname,-1,'_'),yymm6.) between input("&start",yymm6.) and input("&end",yymm6.) 
  ;
quit;

data want;
  length _ds ds $41;
  set &dslist indsname=_ds;
  ds=_ds;
run;

Frequent Contributor
Posts: 142

Re: Concatenate datasets as it get created

Posted in reply to geetha1a2b3c

complete  macrotized code, based on the run date you mention or today's date it will pick the dates as you mentioned and create the data. see if this is helpful.

 

data _null_;

call symputx('run_dt',compress(put(intnx('day',today(),0),yymmn6.)));/*Start date--use %let run_dt=value if want other dates*/

call symputx('dtp6mon1',compress(put(intnx('month',today(),-6),yymmn6.))); /*6months previous date to current date**/

call symputx('dtp6mon2',compress(put(intnx('month',today(),-12),yymmn6.)));/*6months previous date to dtp6mon1...**/

call symputx('dtp6mon3',compress(put(intnx('month',today(),-18),yymmn6.)));

call symputx('dtp6mon4',compress(put(intnx('month',today(),-24),yymmn6.)));

call symputx('dtp6mon5',compress(put(intnx('month',today(),-30),yymmn6.)));

call symputx('dtp6mon6',compress(put(intnx('month',today(),-36),yymmn6.)));

run;

%put run_dt=&run_dt.;

%put dtp6mon1=&dtp6mon1.;

%put dtp6mon2=&dtp6mon2.;

%put dtp6mon3=&dtp6mon3.;

%put dtp6mon4=&dtp6mon4.;

%put dtp6mon5=&dtp6mon5.;

%put dtp6mon6=&dtp6mon6.;

 

data new;

set s_&dtp6mon6. s_&dtp6mon5. s_&dtp6mon4. s_&dtp6mon3. s_&dtp6mon2. s_&dtp6mon1.;

run;

 

Thanks,

Vish

Ask a Question
Discussion stats
  • 6 replies
  • 157 views
  • 0 likes
  • 4 in conversation