topic Re: macro variable in SAS Programming

macro variable

JShi — Tue, 27 Aug 2019 18:49:21 GMT

I tried to import data from excel, the method1 does not work, but the method2 work, I don't know why.

(1) method1:

%let patients=002 003 004;
%let j=2;
%let shortID=%qscan(%superQ(Patients),&j.);*the patient ID of the jth patient from the Patients list;
filename wear "/data/work/sasusers/jxs004/GingerYang/ReAct/data/original data from ReAct/Actigraph New/&shortID./&shortID. scoring with sleep.xlsx";
PROC IMPORT OUT=wear&shortID. DATAFILE=wear DBMS=xlsx REPLACE; SHEET="Daily"; GETNAMES=YES;RUN;

(2)method2:

%let shortID=003;
filename wear "/data/work/sasusers/jxs004/GingerYang/ReAct/data/original data from ReAct/Actigraph New/&shortID./&shortID. scoring with sleep.xlsx";
PROC IMPORT OUT=wear&shortID. DATAFILE=wear DBMS=xlsx REPLACE; SHEET="Daily"; GETNAMES=YES;RUN;

Re: macro variable

PaigeMiller — Tue, 27 Aug 2019 19:00:00 GMT

Are there errors in the SAS log?

Show us the log by clicking on the {i} icon and pasting the log into the window that appears. Do not provide the log any other way. Thank you.

Re: macro variable

Reeza — Tue, 27 Aug 2019 19:02:10 GMT

Add the following options to your code, and check your log.

options mprint symbolgen;

Put that before your code, run it and then check your log.

@JShi wrote:

I tried to import data from excel, the method1 does not work, but the method2 work, I don't know why.

(1) method1:

%let patients=002 003 004;
%let j=2;
%let shortID=%qscan(%superQ(Patients),&j.);*the patient ID of the jth patient from the Patients list;
filename wear "/data/work/sasusers/jxs004/GingerYang/ReAct/data/original data from ReAct/Actigraph New/&shortID./&shortID. scoring with sleep.xlsx";
PROC IMPORT OUT=wear&shortID. DATAFILE=wear DBMS=xlsx REPLACE; SHEET="Daily"; GETNAMES=YES;RUN;

(2)method2:

%let shortID=003;
filename wear "/data/work/sasusers/jxs004/GingerYang/ReAct/data/original data from ReAct/Actigraph New/&shortID./&shortID. scoring with sleep.xlsx";
PROC IMPORT OUT=wear&shortID. DATAFILE=wear DBMS=xlsx REPLACE; SHEET="Daily"; GETNAMES=YES;RUN;

Re: macro variable

JShi — Tue, 27 Aug 2019 19:06:33 GMT

the information in log:

23 options symbolgen mlogic mprint;
24 %let patients=002 003 004;
25 %let j=2;
26 %let shortID=%qscan(%superQ(Patients),&j.);*the patient ID of the jth patient from the Patients list;
SYMBOLGEN: Macro variable J resolves to 2
27 filename wear "/data/work/sasusers/jxs004/GingerYang/ReAct/data/original data from ReAct/Actigraph
27 ! New/&shortID./&shortID. scoring with sleep.xlsx";
SYMBOLGEN: Macro variable SHORTID resolves to 003
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable SHORTID resolves to 003
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

28 PROC IMPORT OUT=wear&shortID. DATAFILE=wear DBMS=xlsx REPLACE; SHEET="Daily"; GETNAMES=YES;RUN;
SYMBOLGEN: Macro variable SHORTID resolves to 003
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "SHORTID".
28 wear003
___
22
___
200
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE,
_DEBUG_.

ERROR 200-322: The symbol is not recognized and will be ignored.

Re: macro variable

PaigeMiller — Tue, 27 Aug 2019 19:07:14 GMT

Please click on the {i} icon and paste your log into the window that appears.

Re: macro variable

Quentin — Tue, 27 Aug 2019 19:55:05 GMT

I suspect the macro quoting is causing problems. It *should* work fine, but sometimes that macro quoting is not automatically removed like it should be.

Does this work:

%let patients=002 003 004;
%let j=2;
%let shortID=%scan(&Patients,&j.);

If it does, that would confirm it's a macro unquoting problem. You could either avoid the macro quoting (as above) or explicitly %unquote the value with something like:

PROC IMPORT OUT=wear%unquote(&shortID)

Re: macro variable

Astounding — Tue, 27 Aug 2019 19:58:01 GMT

Don't quote strings unless they require quoting.

The solution to your problem might be as simple as replacing %QSCAN with %SCAN. Give it a try and see if that does the trick.

Re: macro variable

ballardw — Tue, 27 Aug 2019 20:27:06 GMT

Why all the quoting in

%let shortID=%qscan(%superQ(Patients),&j.);*

You are potentially introducing artifacts that aren't needed:

%let shortid= %scan(&patients,&j.);
%put shortid=&shortid.;