topic Re: call variable names in a loop in SAS Programming

call variable names in a loop

Marzi — Wed, 26 Oct 2016 03:27:15 GMT

I want to create a loop for calling variable names, such at i-th loop, the name of the first i column will display, however, my code is only working for the first loop. this is my code:

DATA TEMP;
 INPUT ID $ NAME $ SALARY DEPARTMENT $;
 DATALINES;
 1 Rick 623.3 IT
 2 Dan 515.2 Operations
 3 Michelle 611 IT
 4 Ryan 729 HR
 5 Gary 843.25 Finance
 6 Nina 578 IT
 7 Simon 632.8 Operations
 8 Guru 722.5 Finance
 ;
RUN;

%macro freqbynum(data=,num=); 
 %local varlist;
 proc transpose data=&data(obs=0) out=varnames;
 var _all_;
 run;
 PROC SQL;
 CREATE TABLE &data AS
 SELECT * FROM varnames(obs=&num);
 QUIT;
 run;
 PROC PRINT data = &data;
 RUN;
%mend; 
%MACRO loop;
 %DO i = 1 %to 4;
 %freqbynum(data= TEMP ,num= &i);
 %END;
%MEND loop;
%loop;

I'd appreciate it if someone tells me what is my mistake, and how I can figure it out?

Re: call variable names in a loop

Reeza — Wed, 26 Oct 2016 03:07:25 GMT

If your code is as shown, you declare your freqbynum macro AFTER you call it. It needs to exist before.

Order of operation.

Re: call variable names in a loop

Marzi — Wed, 26 Oct 2016 03:23:34 GMT

the problem is not from the order of MACRO, I fix it, but still getting the same answer.

Re: call variable names in a loop

Reeza — Wed, 26 Oct 2016 03:33:03 GMT

Why not query the sashelp.vcolumn data instead?

I dont see anything in your code. Post the full log using MPRINT and SYMBOLGEN

Re: call variable names in a loop

Ksharp — Wed, 26 Oct 2016 03:41:56 GMT

You overwrite the original table. That is not right.

DATA TEMP;
 INPUT ID $ NAME $ SALARY DEPARTMENT $;
 DATALINES;
 1 Rick 623.3 IT
 2 Dan 515.2 Operations
 3 Michelle 611 IT
 4 Ryan 729 HR
 5 Gary 843.25 Finance
 6 Nina 578 IT
 7 Simon 632.8 Operations
 8 Guru 722.5 Finance
 ;
RUN;

%macro freqbynum(data=,num=); 
 %local varlist;
 proc transpose data=&data(obs=0) out=_varnames;
 var _all_;
 run;
 PROC SQL;
 CREATE TABLE _&data AS
 SELECT * FROM _varnames(obs=&num);
 QUIT;
 run;
 PROC PRINT data = _&data;
 RUN;
%mend; 
%MACRO loop;
 %DO i = 1 %to 4;
 %freqbynum(data= TEMP ,num= &i);
 %END;
%MEND loop;
%loop;

Re: call variable names in a loop

Ksharp — Wed, 26 Oct 2016 03:44:38 GMT

Or Simple as IML.

DATA TEMP;
 INPUT ID $ NAME $ SALARY DEPARTMENT $;
 DATALINES;
 1 Rick 623.3 IT
 2 Dan 515.2 Operations
 3 Michelle 611 IT
 4 Ryan 729 HR
 5 Gary 843.25 Finance
 6 Nina 578 IT
 7 Simon 632.8 Operations
 8 Guru 722.5 Finance
 ;
RUN;

proc iml;
x=contents(temp);
do i=1 to nrow(x);
print (x[1:i]);
end;
quit;

Re: call variable names in a loop

Marzi — Wed, 26 Oct 2016 03:52:20 GMT

1365      DATALINES;

NOTE: The data set WORK.TEMP has 8 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


1374          ;
1375  RUN;
1376
1377  %macro freqbynum(data=,num=);
1378      %local varlist;
1379      proc transpose data=&data(obs=0) out=varnames;
1380          var _all_;
1381      run;
1382      PROC SQL;
1383          CREATE TABLE &data AS
1384          SELECT * FROM varnames(obs=&num);
1385      QUIT;
1386      run;
1387      PROC PRINT data = &data;
1388      RUN;
1389  %mend;
1390  %MACRO loop;
1391      %DO i = 1 %to 4;
1392         %freqbynum(data= TEMP ,num= &i);
1393      %END;
1394  %MEND loop;
1395
1396  options mprint mlogic SYMBOLGEN;
1397  %loop;
MLOGIC(LOOP):  Beginning execution.
MLOGIC(LOOP):  %DO loop beginning; index variable I; start value is 1; stop value is 4; by value is 1.
MLOGIC(FREQBYNUM):  Beginning execution.
SYMBOLGEN:  Macro variable I resolves to 1
MLOGIC(FREQBYNUM):  Parameter DATA has value TEMP
MLOGIC(FREQBYNUM):  Parameter NUM has value 1
MLOGIC(FREQBYNUM):  %LOCAL  VARLIST
SYMBOLGEN:  Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM):   proc transpose data=TEMP(obs=0) out=varnames;
MPRINT(FREQBYNUM):   var _all_;
MPRINT(FREQBYNUM):   run;

NOTE: Numeric variables in the input data set will be converted to character in the output data set.
NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.VARNAMES has 4 observations and 1 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(FREQBYNUM):   PROC SQL;
SYMBOLGEN:  Macro variable DATA resolves to TEMP
SYMBOLGEN:  Macro variable NUM resolves to 1
MPRINT(FREQBYNUM):   CREATE TABLE TEMP AS SELECT * FROM varnames(obs=1);
NOTE: Table WORK.TEMP created, with 1 rows and 1 columns.

MPRINT(FREQBYNUM):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(FREQBYNUM):   run;
SYMBOLGEN:  Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM):   PROC PRINT data = TEMP;
MPRINT(FREQBYNUM):   RUN;

NOTE: There were 1 observations read from the data set WORK.TEMP.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.05 seconds
      cpu time            0.01 seconds


MLOGIC(FREQBYNUM):  Ending execution.
MPRINT(LOOP):  ;
MLOGIC(LOOP):  %DO loop index variable I is now 2; loop will iterate again.
MLOGIC(FREQBYNUM):  Beginning execution.
SYMBOLGEN:  Macro variable I resolves to 2
MLOGIC(FREQBYNUM):  Parameter DATA has value TEMP
MLOGIC(FREQBYNUM):  Parameter NUM has value 2
MLOGIC(FREQBYNUM):  %LOCAL  VARLIST
SYMBOLGEN:  Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM):   proc transpose data=TEMP(obs=0) out=varnames;
MPRINT(FREQBYNUM):   var _all_;
MPRINT(FREQBYNUM):   run;

NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.VARNAMES has 1 observations and 2 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(FREQBYNUM):   PROC SQL;
SYMBOLGEN:  Macro variable DATA resolves to TEMP
SYMBOLGEN:  Macro variable NUM resolves to 2
MPRINT(FREQBYNUM):   CREATE TABLE TEMP AS SELECT * FROM varnames(obs=2);
NOTE: Table WORK.TEMP created, with 1 rows and 2 columns.

MPRINT(FREQBYNUM):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


MPRINT(FREQBYNUM):   run;
SYMBOLGEN:  Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM):   PROC PRINT data = TEMP;
MPRINT(FREQBYNUM):   RUN;

NOTE: There were 1 observations read from the data set WORK.TEMP.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds


MLOGIC(FREQBYNUM):  Ending execution.
MPRINT(LOOP):  ;
MLOGIC(LOOP):  %DO loop index variable I is now 3; loop will iterate again.
MLOGIC(FREQBYNUM):  Beginning execution.
SYMBOLGEN:  Macro variable I resolves to 3
MLOGIC(FREQBYNUM):  Parameter DATA has value TEMP
MLOGIC(FREQBYNUM):  Parameter NUM has value 3
MLOGIC(FREQBYNUM):  %LOCAL  VARLIST
SYMBOLGEN:  Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM):   proc transpose data=TEMP(obs=0) out=varnames;
MPRINT(FREQBYNUM):   var _all_;
MPRINT(FREQBYNUM):   run;

NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.VARNAMES has 2 observations and 2 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(FREQBYNUM):   PROC SQL;
SYMBOLGEN:  Macro variable DATA resolves to TEMP
SYMBOLGEN:  Macro variable NUM resolves to 3
MPRINT(FREQBYNUM):   CREATE TABLE TEMP AS SELECT * FROM varnames(obs=3);
NOTE: Table WORK.TEMP created, with 2 rows and 2 columns.

MPRINT(FREQBYNUM):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.03 seconds


MPRINT(FREQBYNUM):   run;
SYMBOLGEN:  Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM):   PROC PRINT data = TEMP;
MPRINT(FREQBYNUM):   RUN;

NOTE: There were 2 observations read from the data set WORK.TEMP.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds


MLOGIC(FREQBYNUM):  Ending execution.
MPRINT(LOOP):  ;
MLOGIC(LOOP):  %DO loop index variable I is now 4; loop will iterate again.
MLOGIC(FREQBYNUM):  Beginning execution.
SYMBOLGEN:  Macro variable I resolves to 4
MLOGIC(FREQBYNUM):  Parameter DATA has value TEMP
MLOGIC(FREQBYNUM):  Parameter NUM has value 4
MLOGIC(FREQBYNUM):  %LOCAL  VARLIST
SYMBOLGEN:  Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM):   proc transpose data=TEMP(obs=0) out=varnames;
MPRINT(FREQBYNUM):   var _all_;
MPRINT(FREQBYNUM):   run;

NOTE: There were 0 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.VARNAMES has 2 observations and 2 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(FREQBYNUM):   PROC SQL;
SYMBOLGEN:  Macro variable DATA resolves to TEMP
SYMBOLGEN:  Macro variable NUM resolves to 4
MPRINT(FREQBYNUM):   CREATE TABLE TEMP AS SELECT * FROM varnames(obs=4);
NOTE: Table WORK.TEMP created, with 2 rows and 2 columns.

MPRINT(FREQBYNUM):   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


MPRINT(FREQBYNUM):   run;
SYMBOLGEN:  Macro variable DATA resolves to TEMP
MPRINT(FREQBYNUM):   PROC PRINT data = TEMP;
MPRINT(FREQBYNUM):   RUN;

NOTE: There were 2 observations read from the data set WORK.TEMP.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds


MLOGIC(FREQBYNUM):  Ending execution.
MPRINT(LOOP):  ;
MLOGIC(LOOP):  %DO loop index variable I is now 5; loop will not iterate again.
MLOGIC(LOOP):  Ending execution.

The Full Log

Re: call variable names in a loop

RW9 — Wed, 26 Oct 2016 08:34:16 GMT

I totally agree with @Reeza here, your code doesn't really do any that this wouldn't do in a far more succient way:

data want;
  set sashelp.vcolumn (where=(libname="WORK" and memname="TEMP") keep=name);
run;

Re: call variable names in a loop

Marzi — Fri, 28 Oct 2016 03:31:52 GMT

Thanks, @RW9 I need to call variables by their position, rather than their name

Re: call variable names in a loop

Reeza — Fri, 28 Oct 2016 04:21:10 GMT

@Marzi Please look at the SASHELP.VCOLUMN website. There's a variable that contains the order of the variables, I can't recall the name now. Add a clause to the query

and order < &var_nums

Re: call variable names in a loop

Marzi — Fri, 28 Oct 2016 05:11:06 GMT

Thank @Reeza, it was helpful.