Help using Base SAS procedures

First.variable in proc sql

Reply
Contributor
Posts: 36

First.variable in proc sql

Thank you in advance.

I got into a situation where i have to select first.variable in a proc sql query. for example i have webfocus code like the following which i am migrating into sas.

TABLE FILE DWS_USKMRT
SUM MED_C MED_M DEN_C DEN_M
EMP_GRP_NAME MAX.PPO_FLG
MAX.GROUP_CAT PCN_C PCN_M
MAX.BP CARRIER_NAME CORP
MIN.INOUT FST.MBR_STATE COMP_NAME_INDIC
BY CARRIER
BY EMP_GRP_NBR
BY EMP_DIVISION
ON TABLE HOLD AS TST3A
END
-RUN

For that i wrote my sas code

PROC SQL;
CREATE TABLE TST31 AS
SELECT CARRIER,EMP_GRP_NBR,EMP_DIVISION,MBR_STATE,EMP_GRP_NAME,CARRIER_NAME,
COMP_NAME_INDIC,CORP,SUM(MED_C) AS MED_C,SUM(MED_M) AS MED_M,
SUM(DEN_C) AS DEN_C,SUM(DEN_M)AS DEN_M,MAX(PPO_FLG) as PPO_FLG ,
MAX(IT) AS IT,MAX(ITS_FLAG) AS ITS_FLAG,MAX(GROUP_CAT) AS GROUP_CAT,
SUM(PCN_C)AS PCN_C,SUM(PCN_M) AS PCN_M,MAX(LOB) AS LOB,MAX(BP) AS BP,
MIN(EFF_DATE) AS EFF_DATE,MAX(END_DATE) AS END_DATE,MIN(INOUT) AS INOUT,
MBR_ZIP,CONT_TYPE
FROM DWS_USKMRT
GROUP BY CARRIER,EMP_GRP_NBR,EMP_DIVISION;
QUIT;

PROC SORT DATA=TST31 OUT=TST32;
BY CARRIER EMP_GRP_NBR EMP_DIVISION MBR_STATE;
RUN;

DATA TST33;
SET TST32;
BY CARRIER EMP_GRP_NBR EMP_DIVISION MBR_STATE;
IF FIRST.MBR_STATE THEN OUTPUT;
RUN;

DATA TST34;
SET TST33;
KEY= COMPRESS(CARRIER||EMP_GRP_NBR||EMP_DIVISION);
RUN;
PROC SORT DATA=TST34 OUT=TST35;
BY KEY;
RUN;

But my problem is i am not getting the desired output as it was in webfocus. The MBR_STATE field on which i took first.variable isn't matching to that of webfocus. I tried in my ways but i don't know the right way. is there any way take first.variable in proc sql itself. Thank you.
Ask a Question
Discussion stats
  • 0 replies
  • 823 views
  • 0 likes
  • 1 in conversation