How to compare value of macro variable to normal value in a SAS dataset

[Ravindra_]

I have a macro variable with a subjid entered manually in the program and i also have multiple subjid information in a excel and there is other column in excel which has a identifier as with_coding or without_coding. 

Here i will just pass the subject ID in my program and it has to read automatically the similar subjid from excel and also based on the identifier variable if it is with_coding then it need to pick the program i had developed specifically for that and if it is without_coding then it need to pick specifically the other program.

In here i have developed the whole program

I had used created an external macro variable where i always pass the respective study ID and by using call symput i had changed the variables from excel to macro variable and i am missing something in here which i need support to automatically read the relevant study id from excel.

I hope you understood and will provide me some solution

[Ravindra_]

Just to add the code i had written and please suggest what i am missing in that and what is that i am doing wrong?

%GLOBAL TYPE_ TYPE STUDYID STUDY_ID Current_Study SOD_PATH CSV_PATH ODM_user ODM_pw QCODE_SCHEMA PLATFORM SAS_OUTPUT_PATH;

/*Change the respective study id*/
%let STUDY_ID=ABCDEFG;
%put &STUDY_ID.;

/*Importing the master sheet*/
proc sql ;
connect to excel (path="\\path\Master_sheet.xlsx");
create table Input as 
select * 
from connection to excel 
(select * from [Sheet1$]);
disconnect from excel ;
quit;

/*Macro variables generation*/
data _null_;
    set Input;
    call symput("TYPE",TYPE);
	call symput("STUDYID",STUDYID);
	call symput("SAS_OUTPUT_PATH",SAS_OUTPUT_PATH);
	call symput("CSV_PATH",CSV_PATH);
	call symput("ODM_username",ODM_user);
	call symput("ODM_password",ODM_pw);
	call symput("PLATFORM",PLATFORM);
run;

%put &TYPE.;



/*Program switch between Infrom with coding and Inform without coding*/
%MACRO main;

%IF &TYPE=INFORM_WITHOUT_CODING %THEN %DO;
   %INCLUDE "\\path\Trifacta CSV to SAS - inform.SAS";
	%END;
%ELSE %IF &TYPE=INFORM_WITH_CODING %THEN %DO;
   %INCLUDE "\\path\Trifacta_CSV_to_SAS_ inform_Coding.SAS";
	%END;
%MEND main;

%main;

[PaigeMiller]

Nothing in the code you show remotely matches the title. I don't see where you are trying to compare the value of a macro variable to a value in a SAS data set.

[Ravindra_]

I am trying to compare the study id in %let with the study id from the excel file

[ballardw]

If your Excel source has more than one row of values you have created macro variables for only the last row.

I will suggest that having macro variables just "appear from nowhere" in macro code is one of the potentials for problems. It is generally better to explicitly pass values a parameters. That way someone borrowing the code knows that they have to supply a value. Real problems arise when a macro variable of the same name but unexpected values exist in another users context.

Is the data set value comparison supposed to take place inside the code of those two %include files? If so then you need to share the code for those and point to where you expect the comparisons to take place.

[smantha]

Where are you applying the subject_id filter in the code? Is your excel file unique by study_id? If not which studY_id or type do you wan to process

[ballardw]

You could at least show one example of the macro variable in question.

If your macro variable is supposed to be text you use something like:

if mydatasetvar = "&macrovarname."  then ...

however in this case you may have to consider case if the data set varible has a value of "ABC" and the macro variable is "Abc" and are supposed to match. So you could use either the Upcase or Lowcase functions around both values.

Another concern is if either of the variables potentially have leading spaces ("     ABC"  and "ABC" should match). If that might happen you might need to wrap a Strip function call around both values.

If the macro variable is supposed to be numeric then

if datasetvar = &macrovar.  then ...

[Ravindra_]

I have already tried using that if then but it didn't work.

I will just change the study_id all the time and the similar study id will be present in the excel and the other variable in excel have Infrom with coding and Inform without coding as values and based on these values it need to switch the program

%GLOBAL TYPE_ TYPE STUDYID STUDY_ID Current_Study SOD_PATH CSV_PATH ODM_user ODM_pw QCODE_SCHEMA PLATFORM SAS_OUTPUT_PATH;

/*Change the respective study id*/
%let STUDY_ID=ABCDEFG;
%put &STUDY_ID.;

/*Importing the master sheet*/
proc sql ;
connect to excel (path="\\path\Master_sheet.xlsx");
create table Input as 
select * 
from connection to excel 
(select * from [Sheet1$]);
disconnect from excel ;
quit;

/*Macro variables generation*/
data _null_;
    set Input;
    call symput("TYPE",TYPE);
	call symput("STUDYID",STUDYID);
	call symput("SAS_OUTPUT_PATH",SAS_OUTPUT_PATH);
	call symput("CSV_PATH",CSV_PATH);
	call symput("ODM_username",ODM_user);
	call symput("ODM_password",ODM_pw);
	call symput("PLATFORM",PLATFORM);
run;

%put &TYPE.;



/*Program switch between Infrom with coding and Inform without coding*/
%MACRO main;

%IF &TYPE=INFORM_WITHOUT_CODING %THEN %DO;
   %INCLUDE "\\path\Trifacta CSV to SAS - inform.SAS";
	%END;
%ELSE %IF &TYPE=INFORM_WITH_CODING %THEN %DO;
   %INCLUDE "\\path\Trifacta_CSV_to_SAS_ inform_Coding.SAS";
	%END;
%MEND main;

%main;

[Tom]

How many observations are you reading from the EXCEL file?   The notes in the SAS log will tell you how many observations are in the dataset INPUT.  Your current code is setting the macro variable TYPE from the LAST observation in INPUT.   Is that what you want?  It might be better to use the FIRST observation in INPUT as it is not uncommon for Excel sheets to have a lot of empty rows appended to them.

Also did you intend to store the trailing spaces into the macro variables?  If not then use the more modern CALL SYMPUTX() function instead as it will automatically trim the spaces. Add some non-blank characters around the macro value in your %PUT statement to see them.

data _null_;
    set Input;
    call symputX("TYPE",TYPE);
    call symputX("STUDYID",STUDYID);
    call symputX("SAS_OUTPUT_PATH",SAS_OUTPUT_PATH);
    call symputX("CSV_PATH",CSV_PATH);
    call symputX("ODM_username",ODM_user);
    call symputX("ODM_password",ODM_pw);
    call symputX("PLATFORM",PLATFORM);
    STOP;
run;

%put TYPE=|&TYPE.|;

[ballardw]

Your code shows a %put &type statement.

So what does the LOG show for the value of &type? In fact what does the log show when you run all of this code? Copy from the Log and paste into a code box opened with the </> icon.

You should set

Options mprint;

before executing the macro %main to examine the code actually generated when it executes.

Have

options nomprint after the %main to stop the extra macro output.

One of the very unfortunate behaviors of many Excel file creators in an inability to keep the column headings consistent. It is entirely possibly that the Input data set may not contain one or more of those variables you create macro variables from.

You might also want to consider using Call SymputX instead of Call Symput. That would remove any possibly leading or trailing blanks that may be present in the variables and sometimes causes issues with resolved macro values.

[Ravindra_]

Hi all,

Thank you for your help. I was able to sort it by myself. I had used the in operator near the call symput to compare the normal variable and macro variabale and was able to get the result as expected.

Thank you.