topic how to change libname based on IF statement in macro in SAS Programming

how to change libname based on IF statement in macro

jayakumarmm — Mon, 05 Sep 2016 22:23:57 GMT

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;

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

Astounding — Tue, 06 Sep 2016 00:25:56 GMT

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.

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

jayakumarmm — Tue, 06 Sep 2016 01:05:27 GMT

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;

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

Astounding — Tue, 06 Sep 2016 01:09:51 GMT

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.

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

jayakumarmm — Tue, 06 Sep 2016 02:16:54 GMT

Thank you so much. It is working as expected.

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

Reeza — Tue, 06 Sep 2016 17:27:25 GMT

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.

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

jayakumarmm — Tue, 06 Sep 2016 19:03:21 GMT

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

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

Reeza — Tue, 06 Sep 2016 23:58:34 GMT

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.

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

pri03 — Mon, 30 Jul 2018 20:05:59 GMT

I want to make new libref name and their new path so that my production code is not changed when i work on it.

***********original path*************

libname t "Y:\permanent\final_data.xls";
libname p "Y:\temporary";

**********now i want to have a new libref for p i.e per and path will also be different , similarly for t new libref will be tem and a diff path***********

%let libref = p,t;
%let new_libref=per,tem;
%macro change;
%let wordc= %sysfunc(countw(%bquote(&libref),","));

%put &wordc;
%do i=1 %to &wordc;
%let name = %scan("&libref",&i,",");
%let new_name = %scan("&new_libref",&i,",");
%put &name;
%put &new_name;

%if &name = p %then libname &new_name "Y:\&new_name" ;

%if &name = t %then libname &new_name "Y:\&new_name\final_data.xls";

%end;

%mend change;

%change;

the issue is that only one libref "per" is created and not tem.

help needed ....Thanks in advance.

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

Reeza — Mon, 30 Jul 2018 20:32:26 GMT

@pri03 Please post this as a new thread entirely and you'll get better responses. These old threads will not show up on the page so only users who previously answered this question will see it.

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

Astounding — Mon, 30 Jul 2018 23:00:27 GMT

I'll give you a couple of starting questions, but then as @Reeza suggested, you should post this as a new question.

Why do you want a LIBNAME statement that refers to a particular file? LIBNAME statements should refer to a folder not a file.

Did you notice the solution to the original question on this thread? You don't have any semicolons ending your LIBNAME statements. You only have semicolons ending the %IF %THEN statements that begin the LIBNAME statements.

That's not everything, but it's a good starting point.