BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

Additional question, why are my &oral1 and &oral2 macro variables not being created?

 

%LET Diag=%str(CRC) ; /*for file names: CRC, MM, NSCLC, Unknown*/
LIBNAME dataPrep "C:\data\&diag.\" ;


%LET enddate = 11/1/2016 ;

 

/*for validation*/
%LET adminyr = %scan(%trim(&enddate.),3);
%LET adminmth = %scan(%trim(&enddate.),1);

 

/*orals*/
%macro orals() ;
     %if "&diag."="MM" %then %do ;
     %LET oral1=lenalidomide ;
     %LET oral2=melphalan ;
     %end;
     %else %if "&diag."="CRC" %then %do;
          %LET oral1=capecitabine ;
           %LET oral2=regorafenib ;
          %end ;
%mend ;
%orals;
%put &oral1. &oral2. ;

 

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
They are crated in local symbol table.
Use %global to add the to the global symbol table.
Data never sleeps

View solution in original post

13 REPLIES 13
LinusH
Tourmaline | Level 20
They are crated in local symbol table.
Use %global to add the to the global symbol table.
Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Per the other post on this.  Use datastep - which has the data types, functions, and processing for data items - for processing your data.  You will find it far simpler coding, less messy and more maintainable.  Macro only generates text so your always going to be fighting it.

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

Thanks. I have several long PROC SQLs that use the macro variables so I have to create them outside the datastep. I do additional processing in datasteps but have to pull from SQL first. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Sorry, you seem to be all confused about a fair few things here.

Firstly lets clarify some things.  Macro is not required to do anything in SAS.  In much the same way SQL is not required to do anything in SAS.  Base SAS is a fully feature complete robust language for the processing of data in any shape or size.  If your using SAS, learn Base SAS throughly as that is the tool.

 

Now optionally, once you understand Base SAS, then there are additional components there to aid in certain things.  Macro for instance is there to remove the need to type things repeatedly, it generates the text for you based on some rudimentary logic without types.  It is not designed to process data.  

SQL is an optional component based on ANSI SQL.  It is useful for pulling darta out of SQL databases, and doing some joins/agregates which may require more code in Base SAS.

 

Now then, your process.  It appears you are working with some sort of Microsoft file, Excel/Access?  Not a great idea.  First step would be to bring all the data into SAS in the form of datasets.  This is quite simple, select * from table for each table.  You now have your data in SAS and can use Base SAS to manipulate and process it.

 

I am combining this respose as a reponse to your other question, where you are using the date value with quotes:

%let enddate='11/1/2016';

10854 AND AdministrationDate < &enddate.

So it seems in this "database" you are using you have variables like 11/1/2016.  Aside from the fact that this is really bad practice, as you are now finding trying to access that, it will make all coding effort far more difficult.

 

So go back and look at the whole process.  Extract your data into SAS so you have a nice structured format data to work with and no further connection requirements.  Then design your program flow - not programming first off, design.  How should the data look, will it be simpler to work with if its normalised (data modelling), can I use lag() functions, or perhaps retain.  If not, then the data needs to be array''d, so will renaming the variables make that easier etc.

 

Unfortunately it seems that currently you are wrapped around all kinds of tech and stumbling blocks.

 

Oh, and I don't think your code with '11/1/2016' would work, normally you need to reference non-standard column names with '11/1/2016'n, the n afterwards is important.  

 

Oh, one last point, are you doing pass through?  I ask as you don't mention pulling data over.  Far easier to get the data into SAS, then drop the database connection and use SAS to process it.

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

Thank you for clarifying some things. The goal is to download data into SAS datasets in order to do some additional processing in base SAS. In order to do that I have to run SQL code to access our server data (yes, pass through). We are a new SAS shop (for analytics only) so we have several existing SQL code files which access multiple tables from multiple datasets on our server. The SQL results were copy/pasted into Excel and the process relied on cell formulas with a lot of manual back and forth between SQL and Excel. In order to automate a quarterly process accessing several different sql pulls per project at various stages of the project I want to batch up these SQL pulls to create SAS datasets and to perform additional processes on the SAS data to avoid relying solely on Excel formulas (eventually, using the SAS add-in these datasets will be loaded into various tabs of the Excel workbook which will become the template for future updates). My goal is to create the varying user inputs (dates, drugs, diagnosis, etc) as global variables up front in the program that will be accessed in the PROC SQLs and so the user only needs to input the values and run one program. Mostly I can use LET (or in some case call symput) statements but when the global variables are assigned based on a condition it gets more complicated, especially with dates involved. I have found some tips online that use macros for conditional macro variable assignments so I thought I'd give it a try (and seems to work). Data step with  _NULL_ caused an issue because I didn't have a 'set' dataset that I was working with yet (I need the global variables for the PROC SQL that creates the SAS dataset). The problem I am having with unquoted values in the global variable assignment for dates, I suspect has something to do with using EXECUTE in the PROC SQL (with pass through facility). Most online issues seem to be resolved by adding the single quotes in the %LET assignment value.. Thank you for your help. I am inching my way up the learning ladder and found you and the SAS community a huge resource for me!!!

Tom
Super User Tom
Super User

If you are using macro variables to generate code that will be passed to the remote database then you need to generate the code using the syntax rules for that database.  In SAS you can use either single quotes or double quotes to form a string literal.  In many database system languages you need to use single quotes for string literals and double quotes are used to represent object name literals.  This can be a pain with SAS macro since macro triggers are ignored inside of single quotes.  Here is one way to generate macro variables with single quotes around them.

 

%let date=2016-12-23;
%let qdate=%unquote(%bquote('&date'));
RW9
Diamond | Level 26 RW9
Diamond | Level 26

So to simplfy:

1) You have SQL databases with some scripts.  Set these up to batch run create a set of tables in a specific area.

2) In SAS you have a program which extracts from these set of tables the data into SAS datasets.

3) You have a set of paramters, entered via GUI, or onto text file doesn't really matter.

4) Another SAS program, using your parameter file/input process the data and creates the necessary output files.

 

At no point in the above do I see the need for Excel.  SQL processing should be done on the SQL server.  SAS processing done in SAS.  Only in the transfer of data from SQL to SAS, and from SAS to the output report need the data move at all.

Am off on holiday now.

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9
Using proc SQL with pass thru in addition to data steps and other SAS procs I can do all steps in one sas program (hypothetically that is, although I have multiple programs called from the initial program via %include statements ) . The user input is someone who just enters the values for the %Let statements at the top of the initial program before running it. The resulting SAS datasets get loaded into an excel workbook because we still use excel for client deliverables.
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

also, when I tried creating the variables in a data _NULL_ , it was not understanding the run; statement because nothing other than macro variables were being processed (I think)

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

Tried that for other variables and still having trouble which is why I am switching to macros. This one doesn't like the &year_start1 which was created from the first symput. Yearlotend is a data variable that holds the years 2012, 2013, 2014, 2015, 2016 from a dynamic table produced from a proc freq out= option.  The future PROC SQL looks at the last day of the prior year (12/31/{2 digit prior year} which is why I need to assign a global variable. It doesn't necessarily have to be a two-digit year but it doesn't work even with the full year (yearlotend) entered into the first call symput. If you have any solutions that might help me understand this better would be helpful because if it will be easier than maintaining macros that would be great!

 

data _NULL_;
set yearplan ;
yearlotend2=input(substr(put(yearlotend,4.),3,2),2.)-1 ;
suffix=put(_n_, 5.) ;
call symput(cats('year_start',suffix),yearlotend2);
call symput('projstart', "'"||catx("/","12/31",&year_start1.)||"'") ;
run;
%put &projstart;

Tom
Super User Tom
Super User

Macro variable references are resolved BEFORE the data step runs.  So if you are expecting the second CALL SYMPUT() to reference the macro variable the first one generated you will need to use SYMGET() function.  Or better just use the value you already have in the data step.

 

data _null_;
  set yearplan ;
  suffix=put(_n_, 5.) ;
  yearlotend2=put(mod(yearlotend4-1,100),Z2.);
  call symputX(cats('year_start',suffix),yearlotend2);
  if _n_=1 then call symputX('projstart', quote(catx("/","12/31",yearlotend2))) ;
run;
%put &projstart;

 

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9
thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 8655 views
  • 7 likes
  • 5 in conversation