topic Re: keep macro variable in dataset in SAS Programming

keep macro variable in dataset

leonzheng — Wed, 27 Jun 2018 22:06:07 GMT

I met problem using macro variable in my SAS code.

I need to define a macro variable and keep the column in dataset operation.

%let target_parm = temperature; // so I can change temperature into other parameter later if necessary

//pull data and form a dataset called rawdata which includes a column called temerature

data output (keep = day month year &target_parm.);

set rawdata;

run;

This will return error variable name is not valid.

I saw examples online of referring macro variable usually is &target_parm without ".", but I need to use the form with "." otherwise in some other parts this macro variable will not work (like the operation of pulling temperature data).

Can someone explain to me the difference of w/o "." in &target_parm? And how should I modify the code so the macro variable can be used in keep function.

Thanks,

Re: keep macro variable in dataset

novinosrin — Wed, 27 Jun 2018 22:09:54 GMT

You wrote -" This will return error variable name is not valid"

Why you say that?

It seems to work fine in the below example

%let target_parm = height;

data output (keep = name &target_parm.);

set sashelp.class;

run;

Re: keep macro variable in dataset

leonzheng — Wed, 27 Jun 2018 22:14:10 GMT

It is not working on my side.
ERROR: variable name & is not valid.
ERROR: Invalid for the KEEP option.

Re: keep macro variable in dataset

novinosrin — Wed, 27 Jun 2018 22:16:01 GMT

Please post your code and log

Re: keep macro variable in dataset

leonzheng — Wed, 27 Jun 2018 22:23:43 GMT

/************************ define variable *****************************/
%let target_wafer = 288760;
%let target_op = 9689;
%let target_parm = 'HEATERSUB';
%let PARM = HEATERSUB;

/************************ pull raw data from CHIMP *****************************/
proc sql;
connect to db2 (&wafer_server.);
create table rawdata
as select * from connection to db2 (

SELECT A.WAFERNUM, A.PROCESSDATE, A.HT, A.OP, A.WAFERSIZE, A.PARMNAME, A.ROWNUM, A.COL01,A.COL02,A.COL03,A.COL04,A.COL05,A.COL06,A.COL07,A.COL08,
A.COL09,A.COL10,A.COL11,A.COL12,A.COL13,A.COL14,A.COL15,A.COL16,A.COL17,A.COL18,A.COL19,A.COL20,A.COL21,A.COL22,
A.COL23,A.COL24,A.COL25,A.COL26,A.COL27,A.COL28,A.COL29,A.COL30,A.COL31,A.COL32,A.COL33,A.COL34,A.COL35,A.COL36,
A.COL37,A.COL38,A.COL39,A.COL40,A.COL41,A.COL42,A.COL43,A.COL44,A.COL45,A.COL46,A.COL47,A.COL48,A.COL49,A.COL50,
A.COL51,A.COL52,A.COL53,A.COL54,A.COL55,A.COL56,A.COL57,A.COL58,A.COL59,A.COL60
From wtooldet.CHIMP A
WHERE A.WAFERNUM in (&target_wafer.)
AND A.PARMNAME in (&target_parm.)
AND A.OP in (&target_op.)

order by ROWNUM

);

disconnect from db2;
quit;

proc transpose data=rawdata
out=rawdata_transp
name=COLNUM;
label COLNUM='COLNUM';
by WAFERNUM WAFERSIZE ROWNUM PROCESSDATE;
var COL01 - COL60;
id PARMNAME;
run;

data rawdata_transp (keep = WAFERNUM WAFERSIZE ROWNUM COLNUM &PARM.);
set rawdata_transp;
colnum = substr(COLNUM,4,2);
run;

ERROR:

1303 data rawdata_transp (keep = WAFERNUM WAFERSIZE ROWNUM COLNUM &PARM.);
-
214
23
ERROR 214-322: Variable name & is not valid.

ERROR 23-7: Invalid value for the KEEP option.

-----
23
1303! data rawdata_transp (keep = WAFERNUM WAFERSIZE ROWNUM COLNUM &PARM.);
-----
214
WARNING: Apparent symbolic reference PARM not resolved.
ERROR 214-322: Variable name PARM. is not valid.

1304 set rawdata_transp;
1305 colnum = substr(COLNUM,4,2);
1306 run;

I defined two macro variable to define HEATERSUB w/o '', because the '&PARM.' is not working.

Re: keep macro variable in dataset

novinosrin — Wed, 27 Jun 2018 22:29:25 GMT

That's strange. But can you please run a proc contents to check whether or not there is a variable by the name HEATERSUB in your dataset rawdata_transp?

Re: keep macro variable in dataset

leonzheng — Wed, 27 Jun 2018 22:31:00 GMT

Yes there is

Re: keep macro variable in dataset

Kurt_Bremser — Thu, 28 Jun 2018 07:02:16 GMT

This is the crucial message:

WARNING: Apparent symbolic reference PARM not resolved.

So it seems that your

%let PARM = HEATERSUB;

somehow does not work. I have an inkling you are not showing us all your code.

Add

%put _global_;

at appropriate locations in your code to reveal the status of your global macro symbol table.

Re: keep macro variable in dataset

Kurt_Bremser — Thu, 28 Jun 2018 07:08:25 GMT

PS your basic idea for the algorithm is sound, see this simple example:

data class;
set sashelp.class (keep=name sex);
value = 1;
run;

%let parm=F;

proc transpose data=class out=class1;
by name;
var value;
id sex;
run;

data want;
set class1 (keep=name &parm.);
run;

so it must be something that either prevents the setting of &PARM in the necessary context (eg local vs. global symbol table), or you have some code that clears &parm from the symbol table.

Re: keep macro variable in dataset

leonzheng — Thu, 28 Jun 2018 16:02:06 GMT

thx, I've figure it out, there was something else wrong

Re: keep macro variable in dataset

Kurt_Bremser — Thu, 28 Jun 2018 16:23:32 GMT

It would be nice if you could tell us what exactly happened, as it might help others that are brought to this thread by the search function.

Re: keep macro variable in dataset

leonzheng — Thu, 28 Jun 2018 16:26:34 GMT

The reason is part of my code works in server environment and part is in desktop environment. The macro variable defined on server is not recognized by my local machine.