topic Re: Proc Contents of Directory/Library Compute variables in SAS Programming

Proc Contents of Directory/Library Compute variables

jakestat — Fri, 11 Aug 2017 16:25:38 GMT

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;

Re: Proc Contents of Directory/Library Compute variables

jakestat — Fri, 11 Aug 2017 16:29:01 GMT

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

Re: Proc Contents of Directory/Library Compute variables

PaigeMiller — Fri, 11 Aug 2017 16:51:55 GMT

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.

Re: Proc Contents of Directory/Library Compute variables

jakestat — Fri, 11 Aug 2017 17:02:04 GMT

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?

Re: Proc Contents of Directory/Library Compute variables

PaigeMiller — Fri, 11 Aug 2017 17:06:18 GMT

@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?

Re: Proc Contents of Directory/Library Compute variables

jakestat — Fri, 11 Aug 2017 17:09:29 GMT

The computed variables such as resp_rate;

Re: Proc Contents of Directory/Library Compute variables

jakestat — Fri, 11 Aug 2017 17:11:53 GMT

... and please forgive the missing 'i' in your name. Thank you Paige.

Re: Proc Contents of Directory/Library Compute variables

PaigeMiller — Fri, 11 Aug 2017 17:19:56 GMT

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.

Re: Proc Contents of Directory/Library Compute variables

jakestat — Fri, 11 Aug 2017 17:47:28 GMT

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

Re: Proc Contents of Directory/Library Compute variables

jakestat — Fri, 11 Aug 2017 17:56:50 GMT

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

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

Re: Proc Contents of Directory/Library Compute variables

jakestat — Fri, 11 Aug 2017 18:04:57 GMT

Thank you for your help Paige.