topic Re: First.variable and Last.variable in query builder? in New SAS User

First.variable and Last.variable in query builder?

reminder65 — Tue, 07 May 2019 16:03:17 GMT

Hello, I am a SAS learner, trying to find a way to break down a hand-written code into series of query builders for more user friendly project.

The code is used in the Medical Expenditure Panel Survey workshop and stored in Github website: https://raw.githubusercontent.com/HHS-AHRQ/MEPS/master/SAS/exercise_5a/Exercise5a.sas

This is the output: https://github.com/HHS-AHRQ/MEPS/blob/master/SAS/exercise_5a/Exercise5a_OUTPUT.TXT

This is the part I'm having trouble with. Please notice there is the use of First.variable and Last.variable to distinct two tables. Is there any way this part can be done in the query builder? The goal is to minimize the need to write codes in a program to get the result.

Thank you for help!

PROC SORT DATA=CDATA.H181 (KEEP=DUPERSID DUID CPSFAMID FAMWT15C VARSTR VARPSU TOTSLF15 TTLP15X)
OUT=PERS;
BY DUID CPSFAMID;

DATA PERS2
FAM (KEEP=DUID CPSFAMID FAMSIZE FAMOOP FAMINC);
SET PERS;
BY DUID CPSFAMID;

     LABEL FAMSIZE = '# OF PERSONS PER CPS FAMILY'
           FAMOOP = 'TOTAL OUT-OF-POCKET EXP (TOTSLF15) PER CPS FAMILY'
           FAMINC = 'TOTAL INCOME (TTLP15X) PER CPS FAMILY';

     IF FIRST.CPSFAMID THEN DO;
        FAMSIZE = 0 ;
        FAMOOP = 0 ;
        FAMINC = 0 ;
     END;

     FAMSIZE + 1        ;
     FAMOOP + TOTSLF15 ;
     FAMINC + TTLP15X ;

OUTPUT PERS2;
IF LAST.CPSFAMID THEN OUTPUT FAM;
RUN;

Re: First.variable and Last.variable in query builder?

Reeza — Tue, 07 May 2019 16:45:41 GMT

Query Builder uses SQL which doesn't have the concept of FIRST/LAST.

Since it seems like all it does is create summary statistics you should be able to replace it with a Summary Task though.

@reminder65 wrote:

Hello, I am a SAS learner, trying to find a way to break down a hand-written code into series of query builders for more user friendly project.

The code is used in the Medical Expenditure Panel Survey workshop and stored in Github website: https://raw.githubusercontent.com/HHS-AHRQ/MEPS/master/SAS/exercise_5a/Exercise5a.sas

This is the output: https://github.com/HHS-AHRQ/MEPS/blob/master/SAS/exercise_5a/Exercise5a_OUTPUT.TXT

This is the part I'm having trouble with. Please notice there is the use of First.variable and Last.variable to distinct two tables. Is there any way this part can be done in the query builder? The goal is to minimize the need to write codes in a program to get the result.

Thank you for help!

PROC SORT DATA=CDATA.H181 (KEEP=DUPERSID DUID CPSFAMID FAMWT15C VARSTR VARPSU TOTSLF15 TTLP15X)
              OUT=PERS;
BY DUID CPSFAMID;

DATA PERS2
     FAM (KEEP=DUID CPSFAMID FAMSIZE FAMOOP FAMINC);
SET PERS;
BY DUID CPSFAMID;

     LABEL FAMSIZE = '# OF PERSONS PER CPS FAMILY'
           FAMOOP = 'TOTAL OUT-OF-POCKET EXP (TOTSLF15) PER CPS FAMILY'
           FAMINC = 'TOTAL INCOME (TTLP15X) PER CPS FAMILY';

     IF FIRST.CPSFAMID THEN DO;
        FAMSIZE = 0 ;
        FAMOOP = 0 ;
        FAMINC = 0 ;
     END;

     FAMSIZE + 1        ;
     FAMOOP + TOTSLF15 ;
     FAMINC + TTLP15X ;

     OUTPUT PERS2;
     IF LAST.CPSFAMID THEN OUTPUT FAM;
RUN;

Re: First.variable and Last.variable in query builder?

ballardw — Tue, 07 May 2019 21:36:14 GMT

If you don't actually need the PERS2 data set, which has the increments of things something like this should work from the summary task:

proc summary data=cdata.h181 nway;
   class duid cpsfamid;
   var totslf15 ttlp15x;
   output out=fam sum( totslf15 ttlp15x)= famoop faminc    ;
run;

There is a variable _freq_ by default that has the number of records involved for each combination of duid and cpsfamid.