DATA Step, Macro, Functions and more

how to change libname based on IF statement in macro

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

how to change libname based on IF statement in macro

[ Edited ]

I am trying to change libname based on the IF statement in macro. Currently, libname is not getting assigned.

 

let Filename_1 = FILE1,FILE2;
%let SAS_DATASET = DATA1,DATA2;

%macro file_process;
  %let word_cnt = %sysfunc(countW(%bquote(&Filename_), %str(,)));
  %put NOTE: &=word_cnt;
%do  i = 1 %to &word_cnt;
%let file_name=%scan("&Filename_",&i, ",");
%let dataset=%scan("&SAS_DATASET",&i, ",");
%IF &file_name=File1 %Then libname xlsFile XLSX "/usr/&file1.xlsm";
%IF &file_name=File2 %Then libname xlsFile XLSX "/usr1/&file2.xlsm";
%end
%mend file_process;

Accepted Solutions
Solution
‎09-05-2016 10:16 PM
Super User
Posts: 5,083

Re: how to change libname based on IF statement in macro

Well, half the errors are still there.  So I'm not sure how much help I can be until you fix the other half.

 

At a minimum, you will need two semicolons:

 

%IF &file_name=File1 %Then libname xlsFile XLSX "/usr/2016 log &file_name.xlsm";;

 

That way, one semicolon can end the %IF/%THEN statement, and the second one can end the LIBNAME statement.  But the code won't work until the rest of the errors are corrected. 

View solution in original post


All Replies
Super User
Posts: 5,083

Re: how to change libname based on IF statement in macro

Your code contains numerous errors.  They're all easy to fix, but their presence implies that you need to do some studying on the basics of using macro language.

 

&Filename_ does not exist, you probably meant &Filename_1 but that's not 100% clear since you refer to &Filename_ twice.

 

How could you ever find a match by comparing &file_name to File1?  The only possible values are entered in uppercase on your very first statement.  In macro language, File1 is different than FILE1.

 

Macro language does not use quotes to define character strings.  "&Filename_1" and "&SAS_DATASET" should not be in quotes.

 

You are asking for trouble by using a comma as a delimiter.  Blanks are usually acceptable but if your filenames might contain a blank, choose some other delimiter (not a comma).

 

The correct way to refer to a comma as a delimiter in macro language is %str(,).  Your choice of "," is actually treating both commas and double quotes as delimiters.  That might be a happy accident in this case, because you added quotes around "&Filename_" and "&SAS_DATASET".

 

If your code had successfully generated a LIBNAME statement, it would not have been a complete LIBNAME statement.  It would have been missing a semicolon at the end.  The semicolons that appear now are ending the %IF/%THEN statements, and would not be part of any LIBNAME statements.

 

And the %END statement is missing a semicolon.

 

None of these are large mistakes.  But you do have some studying ahead of you.

Contributor
Posts: 55

Re: how to change libname based on IF statement in macro

Thank you for you response.

Below is the modified code, 90% issues you mentioned is acutally due to modified sample code from Original code.

 

Logic: I have different set of file name patterns so I am trying to use if statements to modify libname to accomadate the changes based on the each file_name pattern.

 

I have a problem in defining libname in IF statement, please let me know if there are any other way to do it. 

 

Issue: Libname is not assgined.

 

 

%let Filename_1 = FILE1,FILE2;
%let SAS_DATASET = DATA1,DATA2;

%macro file_process;
  %let word_cnt = %sysfunc(countW(%bquote(&Filename_1), %str(,)));
  %put NOTE: &=word_cnt;
%do  i = 1 %to &word_cnt;
%let file_name=%scan("&Filename_1",&i, ",");
%let dataset=%scan("&SAS_DATASET",&i, ",");
%IF &file_name=File1 %Then libname xlsFile XLSX "/usr/2016 log &file_name.xlsm";
%IF &file_name=File2 %Then libname xlsFile XLSX "/usr1/2016 log &file_name final.xlsm";
%end;
%mend file_process;

 

Solution
‎09-05-2016 10:16 PM
Super User
Posts: 5,083

Re: how to change libname based on IF statement in macro

Well, half the errors are still there.  So I'm not sure how much help I can be until you fix the other half.

 

At a minimum, you will need two semicolons:

 

%IF &file_name=File1 %Then libname xlsFile XLSX "/usr/2016 log &file_name.xlsm";;

 

That way, one semicolon can end the %IF/%THEN statement, and the second one can end the LIBNAME statement.  But the code won't work until the rest of the errors are corrected. 

Contributor
Posts: 55

Re: how to change libname based on IF statement in macro

Thank you so much. It is working as expected.
Super User
Posts: 17,840

Re: how to change libname based on IF statement in macro

Although you have an answer, your code is very inefficient.

 

Look at the libname function within a data step which may help you avoid macro language at all.

 

You've posted quite a few questions along this same example but they're all a bit scattered so far without the issues truly getting resolved. This type of code will be difficult to maintain and/or expand in the future. It may be worth starting over and explaning what you're trying to do and what's not working.  

Contributor
Posts: 55

Re: how to change libname based on IF statement in macro

Thank you so much for helping me in solving most of the code issues. I completely agree with your point. Given below is the Logic which I am trying to achieve through SAS.

Logic: A set of Excel workbooks(.xlsm & .xlsx) are uploaded into a specific directory in Linux system on monthly basis. There are no unique file name patterns are followed for each workbook but year (i.e. 2016) and prev full month(i.e. August) are the first two characters of file name.

Data movement: Refer a particular sheet name and move the data into teradata table. Complexity in moving
1. reading .xlsm extension files
2. excel sheet names are different
3. Sheet name can be two separate words
4. column names are different in each sheet
5. After a sas dataset is created for each workbook move the data into a teradata table

Super User
Posts: 17,840

Re: how to change libname based on IF statement in macro

1. Use libname methods.

2. You can dynamically determine this if using libname method

3. Use proc copy to import sheet, sheet name is irrelevant. Can also use NLITERAL function to create name so again not a huge issue, unless you're trying to deal with macro variables.

4. Are the columns in the same order? If not how do you know which columns match up to what?

5. Once that process is defined again, should be a libname and set statements. 

 

For Step 1, this is for Windows, for Unix find the relevant command (LS). There are methods if you don't have PIPE access. 

http://support.sas.com/kb/45/805.html

 

Create the macro that imports sheets from workbook and uploads to Teradata. This is the macro that is called for each iteration of the files from step 1. You can use call execute to run the macro.

Unless you need to import all before the upload?

 

You're current process is hard coding many things whereas dynamic processing would help. 

You can also use the file options within a PIPE method to determine the latest files in a folder if required.

 

 

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 444 views
  • 0 likes
  • 3 in conversation