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
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;
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.
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.
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.
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;
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;
Thanks a lot!
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.
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.