DATA Step, Macro, Functions and more

PROC SQL macro

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

PROC SQL macro

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;


Accepted Solutions
Solution
‎07-14-2015 01:22 PM
PROC Star
Posts: 1,235

Re: PROC SQL macro

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

Would expect to see:

  %SQLLOOP;

to call the macro.

View solution in original post


All Replies
PROC Star
Posts: 1,235

Re: PROC SQL macro

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.

Contributor
Posts: 32

Re: PROC SQL macro

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.

Contributor
Posts: 32

Re: PROC SQL macro

Yeah I meant NYR=10 actually

Super User
Posts: 10,527

Re: PROC SQL macro

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 ?

Contributor
Posts: 32

Re: PROC SQL macro

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

Solution
‎07-14-2015 01:22 PM
PROC Star
Posts: 1,235

Re: PROC SQL macro

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

Would expect to see:

  %SQLLOOP;

to call the macro.

Contributor
Posts: 32

Re: PROC SQL macro

Quentin,

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

Super User
Posts: 17,898

Re: PROC SQL macro

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

Contributor
Posts: 32

Re: PROC SQL macro

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 289 views
  • 0 likes
  • 4 in conversation