Hi SAS Users,
Today I ran a macro,
%macro ImportAndTranspose(
File=
, cur=
, outf=
, StartSheet=
, EndSheet=
);
I have the mprint code is as below:
MPRINT(IMPORTANDTRANSPOSE): proc import datafile= "C:\Users\pnguyen\Desktop\New folder\United KingdomEUR" out= United
Kingdom_sheet1 dbms= xlsx replace;
MPRINT(IMPORTANDTRANSPOSE): range= "Sheet1$A:X";
MPRINT(IMPORTANDTRANSPOSE): getnames= yes;
MPRINT(IMPORTANDTRANSPOSE): run;
It seems that there is no abnormal thing can be seen by me from such a code. However, when running, there is a problem popping up:
1 + proc import datafile= "C:\Users\pnguyen\Desktop\New folder\United KingdomEUR" out= United
1 !+Kingdom_sheet1 dbms= xlsx replace; range= "Sheet1$A:X";
______________
22
202
1 !+getnames= yes; run;
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE,
_DEBUG_.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
NOTE: Line generated by the CALL EXECUTE routine.
2 + proc sort data= United Kingdom_sheet1; by Type; run;
______________
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT,
EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS,
NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, NOUNIKEY, NOUNIKEYS,
NOUNIQUEKEY, NOUNIQUEKEYS, NOUNIQUEREC, NOUNIQUERECS, NOUNIREC, NOUNIRECS, OSA, OUT, OVERWRITE, PAGESIZE, PRESORTED,
PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI, THREADS, UNIOUT,
UNIQUEOUT, WKNO, WORKNO.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
I guess the problem popping up because of the blank in the middle of the out data but I do not know how to deal with it, It seems to me no problem here. It is how I get my input for such a macro, including the outf.
filename mydir 'C:\Users\pnguyen\Desktop\New folder';
data _null_;
did = dopen('mydir');
do i = 1 to dnum(did);
fname = scan(dread(did,i),1,'.');
/*fname: United KingdomGBP*/
length short_fn $29 currency $3 ;
short_fn= cats(substr(fname, 1,length(fname)-3),'_');
currency=substr(fname,length(fname)-2);
cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\',
strip(fname),
',cur=',currency,
',outf=',short_fn,'sheet,startsheet=1,endsheet=45);');
call execute(cmd);
end;
keep fname;
run;
I do not post my full code here that I do not want to confuse you, but it is the code 1 from this topic
Thank you and warm regards!
It seem like there's a bug but it avoids a bunch of issues, such as knowing to need the number of sheets or name of sheets ahead of time.
Your process is taking a data set and then you're trying to sort it but you've named the data set United Kingdom_SHeet but that isn't a valid SAS data set name, so that's the issue you need to resolve. I would resolve it within the macro first, the data step is not the problem. Basically you're generating code with syntax errors so it's throwing an error. Or create a new macro variable that doesn't have that issue within the macro somewhere, especially since this seems like a temp step.
I would also highly recommend you add a step that cleans up your process by deleting the temporary files created. This helps prevent you from using old tables when doing a new run in case the code still has errors.
Hi @Reeza
Thank you for your comment! I need to admit that I did it on purpose because I want to keep the full name "United Kingdom" rather than "UnitedKingdom" or "United_Kingdom" because of some following purposes. Is there any way to do so, or I must make sure there is no blank strictly?
Many thanks!
It seem like there's a bug but it avoids a bunch of issues, such as knowing to need the number of sheets or name of sheets ahead of time.
Your process is taking a data set and then you're trying to sort it but you've named the data set United Kingdom_SHeet but that isn't a valid SAS data set name, so that's the issue you need to resolve. I would resolve it within the macro first, the data step is not the problem. Basically you're generating code with syntax errors so it's throwing an error. Or create a new macro variable that doesn't have that issue within the macro somewhere, especially since this seems like a temp step.
I would also highly recommend you add a step that cleans up your process by deleting the temporary files created. This helps prevent you from using old tables when doing a new run in case the code still has errors.
@Phil_NZ wrote:
Hi @Reeza
Thank you for your comment! I need to admit that I did it on purpose because I want to keep the full name "United Kingdom" rather than "UnitedKingdom" or "United_Kingdom" because of some following purposes. Is there any way to do so, or I must make sure there is no blank strictly?
Many thanks!
DON'T.
It would force you to use name literals ever after.
If you want fancy text associated with a dataset, use the dataset label, and retrieve it for a TITLE or similar when you do the report. But in code, always use valid SAS names.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.