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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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

Would expect to see:

  %SQLLOOP;

to call the macro.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

9 REPLIES 9
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
kodmfl
Obsidian | Level 7

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.

kodmfl
Obsidian | Level 7

Yeah I meant NYR=10 actually

ballardw
Super User

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 ?

kodmfl
Obsidian | Level 7

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

Quentin
Super User

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

Would expect to see:

  %SQLLOOP;

to call the macro.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
kodmfl
Obsidian | Level 7

Quentin,

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

Reeza
Super User

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

kodmfl
Obsidian | Level 7

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.

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
  • 9 replies
  • 1383 views
  • 0 likes
  • 4 in conversation