topic Re: Use different select value in proc sql based on variable in SAS Programming

Use different select value in proc sql based on variable

jerry898969 — Wed, 19 Dec 2018 21:25:44 GMT

Hello,

I have a program that I run manually for different years. The sas data table for almost all are in one directory structure and then I have another directory structure for some of the other years.

Ex.

2000-2005 C:\Data\Checking\SAS\Proj1

2006-2009 C:\Data\Checking\SAS\Proj2

The table I want is in that path for that specific year. The issue I have is that one column is different between the two locations.

I have State in the first path and ST in the tables in the second path.

This is what I have tried:

%let year=2009 ;
%macro path ;    
	%if &year. < 2006 %then %let vName = state ;	
	%else %let vName = st ;		
%mend path ;
%path ;

%put &vName. ;

I don't want to manually change these field names in my selects statements. What would be the best way to define a macro variable that will hold either State or ST and use that in my select statements throughout my program?

My example above is giving me "WARNING: Apparent symbolic reference VNAME not resolved." on %put &vName. ;

proc sql ;

select &vname.

from tblTest

quit ;

Re: Use different select value in proc sql based on variable

r_behata — Wed, 19 Dec 2018 22:35:35 GMT

Try this. This code would conditionally check the year and determines the libname based on the year range and renames the field ST to State only if the year is between 2006 and 2009.

Libname Path1 "C:\Data\Checking\SAS\Proj1";
Libname Path2 "C:\Data\Checking\SAS\Proj2";

%let year=2009;

%macro resolve_dates;
	%if %eval(&year >= 2000 ) && %eval( &year <=  2005) %then
		%do;

			Data want;
				set path1.have;
			run;

		%end;
	%else %if %eval( &year>=2006) && %eval(&year  <= 2009) %then
		%do;

			Data want;
				set path2.have(rename=(ST=state));
			run;

		%end;
%mend;

%resolve_dates

Re: Use different select value in proc sql based on variable

novinosrin — Wed, 19 Dec 2018 22:05:13 GMT

Hi @jerry898969 That is because your vName is a local macro variable. Globalise it like -->


%let year=2009 ;
%macro path ; 
	%global vName; 
	%if &year. < 2006 %then %let vName = state ;	
	%else %let vName = st ;		
%mend path ;
%path ;

%put &vName. ;

@jerry898969 wrote:

My example above is giving me "WARNING: Apparent symbolic reference VNAME not resolved." on %put &vName. ;

proc sql ;

select &vname.

from tblTest

quit ;

Re: Use different select value in proc sql based on variable

ShiroAmada — Thu, 20 Dec 2018 00:09:48 GMT

Try this..

%let year=2009 ;
%macro path ;
%let vName = sex ;	 

%if %eval(&year.<2006) %then %let vName = name ;	

proc sql ;
  select &vname.
  from sashelp.class
quit ;
%mend path ;

%path ;

Changes made are the following:

1. Using %eval

2. Moved the proc step inside the macro (if you want to skip the %global macro variable declaration)

3. When year is different then we change the value to be used for macro variable vName. For this case, I can give macro variable a default value. The value will change when your %if condition has been satisfied.

Re: Use different select value in proc sql based on variable

jerry898969 — Thu, 20 Dec 2018 13:59:55 GMT

Thank you everyone for your replies.

novinosrin,

This worked exactly like i needed it too. Thank you.