macro variable

[JShi]

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;

[PaigeMiller]

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.

[Reeza]

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;

---

[JShi]

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.

[PaigeMiller]

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

[Quentin · Accepted solution]

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)

[Astounding]

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.

[ballardw]

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.;