topic Re: Concatenate datasets as it get created in SAS Programming

Concatenate datasets as it get created

geetha1a2b3c — Tue, 08 May 2018 09:24:09 GMT

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.

Re: Concatenate datasets as it get created

PeterClemmensen — Tue, 08 May 2018 07:56:38 GMT

Use PROC APPEND

proc append base=sample.new_sum data=new_201503;
run;

Re: Concatenate datasets as it get created

geetha1a2b3c — Tue, 08 May 2018 08:20:26 GMT

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

Re: Concatenate datasets as it get created

Vish33 — Tue, 08 May 2018 08:41:13 GMT

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);

Re: Concatenate datasets as it get created

Patrick — Tue, 08 May 2018 09:22:47 GMT

@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;

Re: Concatenate datasets as it get created

Patrick — Tue, 08 May 2018 09:37:42 GMT

@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;

Re: Concatenate datasets as it get created

Vish33 — Tue, 08 May 2018 09:48:04 GMT

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