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;
The log helps! It looks like you never actually call the macro.
Would expect to see:
%SQLLOOP;
to call the 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.
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.
Yeah I meant NYR=10 actually
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 ?
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
The log helps! It looks like you never actually call the macro.
Would expect to see:
%SQLLOOP;
to call the macro.
Quentin,
BAHAHAHAHAAHAHAHA ...ARGHHHH thanks man .... you'll excuse me while I slam my head through my monitor
Have you tried arrays instead of macros? I think it would work well in this case...
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.