topic Re: PROC SQL macro in SAS Programming

PROC SQL macro

kodmfl — Tue, 14 Jul 2015 16:20:18 GMT

For anyone that can help I'm trying to run a data set that contains the following through PROC SQL to calculate differences between each month for across all years by station and season

and make sure I output all variables back into the output data set. I think I have some code that should do it but when I run it the data set I try to merge together comes up empty. Short of generating the code for me does anyone have any clues what I'm doing wrong here? The code runs with no error messages at all.

Note &NYR is a global macro variable set to 10 elsewhere in the program as follows for example:

%LET &NYR=10;

Here's the variables in the input data - all numeric except station, season and _name_:

DATA SKDATA;

STATION SEASON MONTH _NAME_ NYEARS _2003 _2004 _2005 _2006 _2007 _2008 _2009 _2010 _2011 _2012 Y_2003 Y_2004 Y_2005 Y_2006 Y_2007 Y_2008 Y_2009 Y_2010 Y_2011 Y_2012

Here is the code: Any assistance would help.

%MACRO SQLLOOP;

%DO I=0 %TO &NYR;

PROC SQL;

CREATE TABLE SENSLOPE&I AS

SELECT STATION, SEASON, MONTH, _%EVAL(&STY.+&I), _%EVAL(&STY.+&I+1]), Y_%EVAL(&STY.+&I+1), Y_%EVAL(&STY.+&I), _%EVAL(&STY.+&I) - _%EVAL(&STY.+&I+1]) AS D_%EVAL(&STY.+&I+1),

Y_%EVAL(&STY.+&I+1) - Y_%EVAL(&STY.+&I) AS DY_%EVAL(&STY.+&I+1)

FROM SKDATA

GROUP BY STATION, SEASON, MONTH;

QUIT;

%END;

DATA SENSLOPE;MERGE SENSLOPE0-SENSLOPE&NYR; BY STATION SEASON MONTH;RUN;

%MEND SQLLOOP;

RUN;

Re: PROC SQL macro

Quentin — Tue, 14 Jul 2015 16:57:51 GMT

The first %LET statement should be %let NYR=10. NOT %let &NYR=10.

If you turn on options mprint, what do you see in the log after calling the macro? That should show what datasets were created, and how many observations they have.

Re: PROC SQL macro

kodmfl — Tue, 14 Jul 2015 17:00:43 GMT

I tried that and believe it or not I am not getting anything which I don't understand ...and which is why I'm here hat in hand as it were.

Re: PROC SQL macro

ballardw — Tue, 14 Jul 2015 17:00:52 GMT

Without seeing the data I suspect the first thing "wrong" is having the data in the "wide" form of years.

Where does &STY come from? What is its value?(I would guess 2002)

Did you exam for instance the content of SENSLOPE0 and SENSLOPE1?

Did this evolve from a process that wasn't a macro, something that worked for maybe two years ?

Re: PROC SQL macro

kodmfl — Tue, 14 Jul 2015 17:01:36 GMT

Yeah I meant NYR=10 actually

Re: PROC SQL macro

Reeza — Tue, 14 Jul 2015 17:03:11 GMT

Have you tried arrays instead of macros? I think it would work well in this case...

Re: PROC SQL macro

kodmfl — Tue, 14 Jul 2015 17:17:42 GMT

Not sure what you mean by "wide" form of years? Anyway &sty is the starting year in this case 2003 so the first variable is _2003 next column _2004, and so on. Variable contents are numeric values of water quality parameters and take values that can range from very small say 0.001 to 1.000 or much higher 1.0 to 500.0 depending on the parameter.

Did this evolve from a process that wasn't a macro, something that worked for maybe two years ?

Actually no, this was already a macro that I wanted to try and redo using PROC SQL. I think although I can't prove it yet, that by using the BY statement processing of PROC SQL I can reduce the processing time for the macro that was developed previously.

I think there's something wrong with the SQL select statement because I'm getting no warnings no errors just 0 obs. Here's the log. I may have to use more than one select statement. I think it's have trouble with the %EVALs in combination with the commas required in PROC SQL but I don' know.

"SKDATA" range/sheet was successfully created.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 2.14 seconds

cpu time 0.15 seconds

34000

34001 %MACRO SQLLOOP;

34002 %DO I=0 %TO 10;

34003 PROC SQL;

34004 CREATE TABLE SENSLOPE&I AS

34005 SELECT STATION, SEASON, MONTH, _%EVAL(&STY.+&I), _%EVAL(&STY.+&I+1]), Y_%EVAL(&STY.+&I+1),

34005! Y_%EVAL(&STY.+&I), _%EVAL(&STY.+&I) - _%EVAL(&STY.+&I+1]) AS D_%EVAL(&STY.+&I+1)

34006 Y_%EVAL(&STY.+&I+1) - Y_%EVAL(&STY.+&I) AS DY_%EVAL(&STY.+&I+1)

34007 FROM SKDATA

34008 GROUP BY STATION, SEASON, MONTH;

34009 QUIT;

34010 %END;

34011 PROC PRINT DATA=SENSLOPE0;

34012 RUN;

34013 PROC PRINT DATA=SENSLOPE1;

34014 RUN;

34015

34016 DATA SENSLOPE;MERGE SENSLOPE0-SENSLOPE10; BY STATION SEASON MONTH;RUN;

34017 RUN;

34018 %MEND SQLLOOP;

34019

34020 DATA SENSLOPE;SET SENSLOPE;

34021 RUN;

NOTE: There were 0 observations read from the data set WORK.SENSLOPE.

NOTE: The data set WORK.SENSLOPE has 0 observations and 0 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

34022 PROC PRINT DATA=SENSLOPE;

34023 RUN;

NOTE: No variables in data set WORK.SENSLOPE.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

Re: PROC SQL macro

Quentin — Tue, 14 Jul 2015 17:22:40 GMT

The log helps! It looks like you never actually call the macro.

Would expect to see:

%SQLLOOP;

to call the macro.

Re: PROC SQL macro

kodmfl — Tue, 14 Jul 2015 17:27:00 GMT

Reeza

Actually the original code was written using a macro using array statements however I am trying to see if BY statement processing in PROC SQL can reduce processing time on the analyses we are running. The program does many different parameters in combinations of locations and seasons. The macros used for that are fairly complicated and take time to run. I am hoping to use PROC SQL to cut down the run time.

Re: PROC SQL macro

kodmfl — Tue, 14 Jul 2015 17:35:01 GMT

Quentin,

BAHAHAHAHAAHAHAHA ...ARGHHHH thanks man .... you'll excuse me while I slam my head through my monitor