topic Re: SQL MAX for sub groups in SAS Programming

SQL MAX for sub groups

tianna — Thu, 27 Jan 2022 01:42:22 GMT

Hi - I am trying to get SAS to create a new table from the data which includes the maximum from one variable for each plot and year and the average for another variable from each plot and year. Any ideas what is wrong with this code?

proc sql;
create table two as
select plot, year, max(psylla_N_young_L) as maxNymph,avg(NE_avg_BT) as NEavg
from Gen3
group by plot, year;
run;

Re: SQL MAX for sub groups

franriv — Thu, 27 Jan 2022 03:27:29 GMT

Looks good to me.

Maybe try ending the proc sql with "quit;" instead of ending it with "run;".

Re: SQL MAX for sub groups

ballardw — Thu, 27 Jan 2022 05:04:26 GMT

Why to you think something is wrong?

If you have a symptom you should describe it. We won't know what is happening without your details.

Otherwise the question and answers end up like pulling hen's teeth.

Often the place to start sharing is to copy the text of the procedure or data step along with the notes, warnings and/or errors from the LOG for the bit you question. On the forum open a text box using the </> icon that appears above the message window and paste the copied text.

The text box helps as many of the messages in the log may have diagnostic characters that get moved if pasted into the main message windows as it reformats text.

If you do not have a log entry that usually means that something is incomplete in the code.

Re: SQL MAX for sub groups

Kurt_Bremser — Thu, 27 Jan 2022 10:08:17 GMT

Maxim 2 says Read the Log. Therefore it is essential that we can see your log, so please copy/paste the whole log text of your step into a window opened with this button:

Re: SQL MAX for sub groups

tianna — Thu, 27 Jan 2022 14:41:25 GMT

Here is the log with the error code.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 1:12.09
cpu time 0.95 seconds

1315 Proc import
1316 DATAFILE="C:\Users\tianna.dupont\Documents\analysis\Alldataallyrs(2021.12.13)forSAS2.xlsx"
1317 OUT=Thresholds
1318 DBMS=xlsx
1319 REPLACE;
1320 GETNAMES=yes;
1321 run;

NOTE: One or more variables were converted because the data type is not supported by the V9
engine. For more details, run with options MSGLEVEL=I.
NOTE: The import data set has 2278 observations and 88 variables.
NOTE: WORK.THRESHOLDS data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 1.27 seconds
cpu time 1.28 seconds

1322
1323 data Gen3;
1324 set Thresholds;
1325 where PPDD between 2000 and 4000;
1326 run;

NOTE: There were 776 observations read from the data set WORK.THRESHOLDS.
WHERE (PPDD>=2000 and PPDD<=4000);
NOTE: The data set WORK.GEN3 has 776 observations and 88 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

1327
1328 proc sql;
1329 create table two as
1330 select plot, year, max(psylla_N_young_L) as maxNymph,
1331 from Gen3
----
1
1332 group by plot, year;
--
79
WARNING 1-322: Assuming the symbol GE was misspelled as Gen3.

ERROR 79-322: Expecting a FROM.

1333 run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.

Re: SQL MAX for sub groups

Tom — Thu, 27 Jan 2022 14:56:55 GMT

You have an extra comma.

When you have a single statement that spans multiple lines place the continuation characters, like the commas between variables in an SQL SELECT statement or the AND in a series of conditions in an ON or WHERE clause, at the START of the new line instead of the END of the old line. That way it will be easier for a human to scan the SAS code to confirm the formatting is correct.

Re: SQL MAX for sub groups

Kurt_Bremser — Thu, 27 Jan 2022 15:26:57 GMT

You omitted the code that caused the first ERROR. Please post the complete log including all code and the messages resulting form it, and use the button I showed you.

Do not hit "Reply" in the notification email you get, because that leads to a stripped-down interface without most of the options. Click on the thread title, and then hit "Reply" once the discussion is opened in a full-fledged browser window.

Re: SQL MAX for sub groups

tianna — Thu, 27 Jan 2022 15:46:56 GMT

The issue was with the data set. It had periods to replace missing data which had to be removed so it read as numeric. Resolved.