topic Re: Simultaneously add DatasetName & a QTR variable based on DSNNAME across a LIBRARY in SAS Programming

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

Zeus_Olympous — Tue, 13 Jun 2017 09:16:11 GMT

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

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

RW9 — Tue, 13 Jun 2017 09:22:08 GMT

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.

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

Zeus_Olympous — Tue, 13 Jun 2017 09:32:43 GMT

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.

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

RW9 — Tue, 13 Jun 2017 09:49:37 GMT

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.

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

Zeus_Olympous — Tue, 13 Jun 2017 11:10:09 GMT

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;

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

RW9 — Tue, 13 Jun 2017 12:10:44 GMT

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;

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

Zeus_Olympous — Tue, 13 Jun 2017 12:35:36 GMT

Thanks a lot!