I can't find an example using a DATA step with multiple SET statements.   I need to find previous 12 months observations based off a DATETIME value.   I'm pulling previous month easily by: MONTH(DATEPART(DATETIME)) = MONTH(TODAY() -1);   I remember running across an example before but cannot find it. ... View more

I have an obersvation that is 48 characters long. I would like to group the data based off the first 8 characters using the CASE function as follows:   PROC SQL;                                             SELECT DSNAME,                                             SPACE1,                                             SPACE2,                                             SPACE3,                                             SPACE4,                                             SPACE5,                                             SPACE6,                                             CASE DSNAME                                          WHEN CONTAINS 'SYS%' OR 'K%' THEN 'SYSTEM'           WHEN CONTAINS 'ASL%' THEN 'RD'                      WHEN CONTAINS 'FS%' THEN 'FS'                       ELSE 'UNKNOWN'                                     END AS OWNER                                  FROM INPT.DATASETS;                              QUIT;                     My results; every observation has an OWNER of SYSTEM. I would have assumed that not narrowing to the first 8 characters of DSNAME would have given me an even broader range of values for OWNER, but no.   Can someone throw me a bone? Thanks in advance!                             ... View more

Trying to accomplish following: DATA TABLE1; SET TABLE2 TABLE3 TABLE4 TABLE5; KEEP OBS1 OBS2 OBS3; WHERE INTNX('MONTH', OBS4, 0) = INTNX('MONTH', TODAY(), -1); OBS4 is DATETIME21.2 formatted.   I've tried: PMONTH=INTNX('MONTH', OBS4, 0); and it comes back null. ... View more

DATA TABLE1; SET TABLE2 TABLE3 TABLE4; KEEP OBS2 OBS4; RUN; ... View more

Simple data pull, not merging tables or anything. Can I replace null values in PROC SQL statement like I can in the following DATA satement:   DATA TABLE1;   SET TABLE2;     IF OBS1 = . THEN OBS1 = 0; RUN;   I've looked at the IFN function but cannot get it to work or find any example to reference. ... View more

Is the following even possible?   PROC SQL;   CREATE TABLE TEMP AS   SELECT A.OBS1,                  A.DATEPART(DATETIME) AS DATE FORMAT DATE9.,                  A.HOUR(DATETIME) AS HOUR FORMATE TIME9.,                  A.CASE                        WHEN OBS3='GIJeff' THEN                             (OBS4-OBS5)                         END AS OBS2,                  B.AVG(OBS2)   FROM TABLE1 A,               (SELECT OBS2                    FROM TABLE1) B   WHERE OBS6 IN(2,3,4); QUIT;   Basically, can you perform AVERAGE and CASE functions while trying to sub select 1 observations from another table? ... View more

Trying to convert the following into one SQL statement, but can't wrap my head around the IF/THEN conditions. Is it possible to do in one SQL statement?   DATA TABLE1; SET TABLE2; IF OBS1 > 1 THEN OBS3=((OBS4*.01)*(OBS5*215)) / ......; ELSE OBS3=((OBS4*.01)*(OBS5*315)) / ......; IF OBS1 > 100 THEN OBS1 = 100; RUN;   PROC SORT DATA=TABLE1; BY OBS1, OBS2;   PROC SUMMARY DATA=TABLE1; BY OBS1, OBS2; VAR OBS3; OUTPUT OUT=TABLE3; MEAN(OBS3)=AVGOBS;   I know how to obviously get the PROC SORT and PROC SUMMARY accomplished with SQL. Just need help with the IF/THEN in SQL.   PROC SQL; CREATE TABLE3 AS SELECT OBS1, OBS2, AVG(OBS3) AS AVGOBS GROUP BY OBS1, OBS2; QUIT; ... View more