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

I am trying to compute the same variables across an library of datasets.   The problem is, the month and year vars are not included in the files.  The code below interpreds year and month from the file name and is supposed to insert yy for year, and mm for month into the file as new variables.  However, the code listed below is inserting year and month as the year and month of the most recent file read on the last dataset.  Any suggestions?


%Macro Libdata;
libname rtot 'H:/...../.../RTOT';
libname test 'H:/...../.../Test';

proc contents data=rtot._all_ memtype=data out=out noprint;
run;

proc sort data=out;
by memname name; run;

data a;
set out;
by memname name;
if first.memname;
run;

data _null_;
set a end=last;
by memname name;
call symput('DS'|| left(_n_),trim(memname));
call symput('yy',substr(memname,10,2)); /*Var Year is not found in summary data*/
call symput('mm',substr(memname,12,2)); /*Month is not found in summary data*/
if last then call symput('TOTAL',left(_n_));
run;

%do i=1 %to &total;
data test.&&DS&i;
set rtot.&&DS&i;
if count gt 0 then resp_rate=round(usable/count*100,1);
cv=round(cv,.01);
exp_tot=round(exp_tot,1);
MON="&mm"; /*insert month into file*/
Yr="&yy"; /*insert year into file*/
Crop=substr(Varname1,2,3);
run;
%end;

%mend Libdata;

 

Credits to: *http://www2.sas.com/proceedings/sugi27/p084-27.pdf;

   

1 ACCEPTED SOLUTION

Accepted Solutions
jakestat
Obsidian | Level 7

This fixed it.... needed a double &&mm&i.

 

MON="&&mm&i"; /*insert month into file*/
Yr="&&yy&i"; /*insert year into file*/

View solution in original post

10 REPLIES 10
jakestat
Obsidian | Level 7
Sample file name are as listed:
CA_RTTOTS1707
HG_RTTOTS1403
HG_RTTOTS1606
SH_RTTOTS1201
HG_RTTOTS1203
AY_RTTOTS1406
HG_RTTOTS1609
SH_RTTOTS1301
HG_RTTOTS1409
AS_RTTOTS1612
SH_RTTOTS1401
HG_RTTOTS1703
SH_RTTOTS1501
PaigeMiller
Diamond | Level 26
data _null_;
set a end=last;
by memname name;
call symput('DS'|| left(_n_),trim(memname));
call symput('yy',substr(memname,10,2)); /*Var Year is not found in summary data*/
call symput('mm',substr(memname,12,2)); /*Month is not found in summary data*/
if last then call symput('TOTAL',left(_n_));
run;

Here's your problem. Each time the data set executes the current observation, it assigns a value to &yy and to &mm, and when it executes the last observation, this value for &yy and &mm is all that is available in future steps of your code.

 

 

If you want each observation of the data set to have it's own &yy and its own &mm then you probably need to have something like

 

call symput('yy'||left(_n_),substr(memname,10,2));

and similarly adjust the calls to &yy later on in the code.

 

--
Paige Miller
jakestat
Obsidian | Level 7
Page, the computations work such that the other vars are updated. The MON and YY are still coming in as 01 17. Is it possible these values are coming from a permanent Mac definition library?
PaigeMiller
Diamond | Level 26

@jakestat wrote:
Page, the computations work such that the other vars are updated.

Could you please be specific about what you mean? What other variables are updated?

--
Paige Miller
jakestat
Obsidian | Level 7
The computed variables such as resp_rate;
PaigeMiller
Diamond | Level 26

I don't see the relationship of resp_rate to your original question. As far as I can tell from looking at your code, I have provided an answer to your original question.

--
Paige Miller
jakestat
Obsidian | Level 7
Thank you Paige. All files have a new time stamp, the new variables are present but the month and year are static. That is the reason I asked if it is possible that a global permanent var definition is over writing it.

Here is the log of the first file written:
cpu time 0.01 seconds


MPRINT(LIBDATA): data _null_;
MPRINT(LIBDATA): set a end=last;
MPRINT(LIBDATA): by memname name;
MPRINT(LIBDATA): call symput('DS'|| left(_n_),trim(memname));
MPRINT(LIBDATA): call symput('yy'|| left(_n_),substr(memname,10,2));
MPRINT(LIBDATA): call symput('mm'|| left(_n_),substr(memname,12,2));
MPRINT(LIBDATA): if last then call symput('TOTAL',left(_n_));
MPRINT(LIBDATA): run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
2:110 2:155 2:208 3:22
NOTE: There were 90 observations read from the data set WORK.A.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


SYMBOLGEN: Macro variable TOTAL resolves to 90
MLOGIC(LIBDATA): %DO loop beginning; index variable I; start value is 1; stop value is 90; by value is 1.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable DS1 resolves to AS_RTTOTS0612
MPRINT(LIBDATA): data test.AS_RTTOTS0612;
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable DS1 resolves to AS_RTTOTS0612
MPRINT(LIBDATA): set rtot.AS_RTTOTS0612;
MPRINT(LIBDATA): if count gt 0 then resp_rate=round(usable/count*100,1);
MPRINT(LIBDATA): cv=round(cv,.01);
MPRINT(LIBDATA): exp_tot=round(exp_tot,1);
SYMBOLGEN: Macro variable MM resolves to 01
MPRINT(LIBDATA): MON="01";
SYMBOLGEN: Macro variable YY resolves to 17
MPRINT(LIBDATA): Yr="17";
MPRINT(LIBDATA): Crop=substr(Varname1,2,3);
MPRINT(LIBDATA): run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
16879 at 1947:96 15274 at 1947:119
NOTE: There were 89268 observations read from the data set RTOT.AS_RTTOTS0612.
NOTE: The data set TEST.AS_RTTOTS0612 has 89268 observations and 44 variables.
NOTE: DATA statement used (Total process time):
real time 0.55 seconds
cpu time 0.15 seconds
jakestat
Obsidian | Level 7

This fixed it.... needed a double &&mm&i.

 

MON="&&mm&i"; /*insert month into file*/
Yr="&&yy&i"; /*insert year into file*/

jakestat
Obsidian | Level 7
... and please forgive the missing 'i' in your name. Thank you Paige.
jakestat
Obsidian | Level 7
Thank you for your help Paige.

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
  • 10 replies
  • 1405 views
  • 0 likes
  • 2 in conversation