BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zeus_Olympous
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Zeus_Olympous
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Zeus_Olympous
Obsidian | Level 7

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;

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

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
  • 796 views
  • 1 like
  • 2 in conversation