BookmarkSubscribeRSS Feed
geetha1a2b3c
Calcite | Level 5

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.

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

Use PROC APPEND

 

proc append base=sample.new_sum data=new_201503;
run;
geetha1a2b3c
Calcite | Level 5
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
Vish33
Lapis Lazuli | Level 10

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

 

Patrick
Opal | Level 21

@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;
Patrick
Opal | Level 21

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

Vish33
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 979 views
  • 0 likes
  • 4 in conversation