DATA Step, Macro, Functions and more

Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

Dear all, I have a Library WKFTMP that includes groups of datasets that are named e.g

TRD_PRO_TOL_201604, TRD_PRO_TOL_201605, TRD_PRO_TOL_201606 etc. only the month gets changed.......

 

The other group of datasets are named LATONIV_BAG_WAV_201604, LATONIV_BAG_WAV_201605, LATONIV_BAG_WAV_201606 etc. again only the month gets changed...

 

and another group of datasets named as HJK_FLD_201604, HJK_FLD_201605, HJK_FLD_201606 etc.again only the month gets changed.....

 

I would like first to add a variable to each dataset with the non numeric part of my dataset names but BASED ON A CONDITION

-- for the first group I would like to have TRD_PRO (only the two first non-numeric part)

--for the second group I would like to have ALL non-numeric part e.g. LATONIV_BAG_WAV and

--for the third group ONLY the first non-numeric part e.g. TRD......

 

Additionally in the same step or macro I would like to add a variable across all datasets with the  QUARTER that each dataset belongs to based on the YEARMONTH string part of it.

 

I know that INDSNAME feature helps....

 

Any suggestion or hint would be more than welcome thank you in advance


Accepted Solutions
Solution
‎06-13-2017 08:35 AM
Super User
Super User
Posts: 7,407

Re: Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

A minor updated then:

%let libga=libs;

data &libga..trd_pro_tol_201402 &libga..trd_pro_tol_201403 &libga..latoniv_bag_wav_201506; set sashelp.class; run; data want _null_; set sashelp.vtable (where=(libname="&libga." and substr(memname,1,3) in ("TRD","LAT"))); dsname=compress(memname,"_","ka"); dt=input(compress(cats(compress(memname,"","kd"),"01"),"_"),yymmdd10.); call execute(cat('data &libga..',memname,'; set &libga..',memname,'; dsname="',dsname,'"; dt="',put(dt,date9.),'"d; format dt date9.; run;')); run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,407

Re: Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

Just a matter of processing indsname:

data want;
  set trd_pro_tol_:
        latoniv_bag_wav_:
        hjk_fld_: 
        indsname=tmp;
  length dsnam $200;
  dsname=scan(compress(tmp,'','d'),2,'.');
  qtr=input(cats(compress(scan(tmp,2,'.'),"_","kd"),"01"),yymmdd10.);
  format qtr ...;
run;

And pop your required format in where ... is.

Contributor
Posts: 40

Re: Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

Dear RW9,

 

Thank you for your solution...

 

Although I do not want to concatenate them in one dataset like "want"...

 

I would like to add the two variables to each one of the datasets separately without merging/concarenating them..

 

Thank you very much.

Super User
Super User
Posts: 7,407

Re: Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

[ Edited ]

A simple metadata loop should work then:

data trd_pro_tol_201402 trd_pro_tol_201403 latoniv_bag_wav_201506;
  set sashelp.class;
run;

data want _null_;
  set sashelp.vtable (where=(libname="WORK" and substr(memname,1,3) in ("TRD","LAT")));
  dsname=compress(memname,"_","ka");
  dt=input(compress(cats(compress(memname,"","kd"),"01"),"_"),yymmdd10.);
  call execute(cat('data ',memname,'; set ',memname,'; dsname="',dsname,'"; dt="',put(dt,date9.),'"d; format dt date9.; run;')); 
run;

The real trick here is using the sashelp metadata with a where, for each of the rows returned a datastep is created where the dataset in the row is updated with the relevant info.

Contributor
Posts: 40

Re: Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

Dear RW9,

 

Thank you very much it works.

 

Although I am not that experienced with CALL EXECUTE and I fail to do the following:

 

my data files are stored in a library libs (not in WORK) which I have earlier put it in a local macro variable as

%LET=libga;

%LET=libs ;

 

so I would like have something like this

 

call execute(cat('data "&libga."|| ',memname,'; set "&libga."|| ',memname,'; dsname="',dsname,'"; dt="',put(dt,date9.),'"d; format dt date9.; run;'));

 

But I fail the get the right combination.

 

Any suggestion would be much appreciated.
run;

 

 

Solution
‎06-13-2017 08:35 AM
Super User
Super User
Posts: 7,407

Re: Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

A minor updated then:

%let libga=libs;

data &libga..trd_pro_tol_201402 &libga..trd_pro_tol_201403 &libga..latoniv_bag_wav_201506; set sashelp.class; run; data want _null_; set sashelp.vtable (where=(libname="&libga." and substr(memname,1,3) in ("TRD","LAT"))); dsname=compress(memname,"_","ka"); dt=input(compress(cats(compress(memname,"","kd"),"01"),"_"),yymmdd10.); call execute(cat('data &libga..',memname,'; set &libga..',memname,'; dsname="',dsname,'"; dt="',put(dt,date9.),'"d; format dt date9.; run;')); run;
Contributor
Posts: 40

Re: Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY

Thanks a lot!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 144 views
  • 1 like
  • 2 in conversation