Filename importing problem

[Phil_NZ]

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

https://communities.sas.com/t5/SAS-Programming/Substitute-codes-generating-different-results-debug/m...

Thank you and warm regards!

P/S: I want to keep the name "United Kingdom" rather than delete the blank in the middle or put a under space because it relates to my following purposes.

[Reeza]

Use COMPRESS() or COMPBL() to ensure that the filename passed doesn't have any spaces. You need to explicitly deal with that situation.

[Phil_NZ]

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!

[Reeza]

Use the NLITERAL function to refer to it properly.
Personally, I would have the name displayed as "United Kingdom" but would have it stored the SAS data set as UnitedKingdom as that is just simpler. You can use the COMPRESS() just where you need it or use the NLITERAL function instead.

[Reeza]

I'd probably also just use PROC COPY to copy the data sets over though, I wouldn't use multiple PROC EXPORTS.

libname myfiles xlsx 'path to xlsx files';
proc copy in=myfiles out=work;
*select listofSheetNames;
run;quit;

[Phil_NZ]

Hi @Reeza!

Thank you for your insightful suggestion!
" I wouldn't use multiple PROC EXPORTS"? Do you mean PROC IMPORT, please?

Just wondering why many SAS experts do not like PROC IMPORT and suggest me to do libname to import data many times. I tried to use libname but my computer does not allow me to do so as I raised a topic below:

https://communities.sas.com/t5/SAS-Programming/Is-there-anyway-to-import-a-file-xlsm-to-xlsx-in-SAS/...

[Reeza · Accepted solution]

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. 

[Kurt_Bremser]

---

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