topic Re: Data Step with BY and first.AAAA and last.BBBB not updating in SAS Programming

Data Step with BY and first.AAAA and last.BBBB not updating

fpascal — Mon, 02 Apr 2018 04:55:52 GMT

Objective: accumulate emergency department (ed_ct) visits ('0450', '0451', '0452', '0456', '0459', '0981') by desy_sort_key and claim_no and write the columns in edtl.inp_ed_ct_lds2013. Zero out ed_ct accumulator when a new desy_sort_key and claim_no is read.

Problem: ed_ct not accumulating, rev_cntr only shows '0001'.

Code

proc sort data=etl.inp_revenuej_lds2013;
by desy_sort_key claim_no clm_line_num;
run;

data etl.inp_ed_ct_lds2013 (drop=HCPCS_CD REV_CNTR_UNIT_CNT clm_thru_dt nch_clm_type_cd
REV_CNTR_RATE_AMT REV_CNTR_TOT_CHRG_AMT REV_CNTR_NCVRD_CHRG_AMT
REV_CNTR_DDCTBL_COINSRNC_CD REV_CNTR_APC_HIPPS_CD);
set etl.inp_revenuej_lds2013;
by desy_sort_key claim_no;

if first.desy_sort_key and first.claim_no then
ed_ct=0;

if rev_cntr in ('0450', '0451', '0452', '0456', '0459', '0981') then
ed_ct=ed_ct + 1;

if last.desy_sort_key and last.claim_no then output etl.inp_ed_ct_lds2013;
run;

LOG

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

62 proc sort data=etl.inp_revenuej_lds2013;

63 by desy_sort_key claim_no clm_line_num;

64 run;

NOTE: Input data set is already sorted, no sorting done.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

66 data etl.inp_ed_ct_lds2013 (drop=HCPCS_CD REV_CNTR_UNIT_CNT clm_thru_dt nch_clm_type_cd

67 REV_CNTR_RATE_AMT REV_CNTR_TOT_CHRG_AMT REV_CNTR_NCVRD_CHRG_AMT

68 REV_CNTR_DDCTBL_COINSRNC_CD REV_CNTR_APC_HIPPS_CD);

69 set etl.inp_revenuej_lds2013;

70 by desy_sort_key claim_no;

72 if first.desy_sort_key and first.claim_no then

73 ed_ct=0;

75 if rev_cntr in ('0450', '0451', '0452', '0456', '0459', '0981') then

76 ed_ct=ed_ct + 1;

78 if last.desy_sort_key and last.claim_no then output etl.inp_ed_ct_lds2013;

79 run;

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line):(Column).

382770 at 76:15

NOTE: There were 9856834 observations read from the data set ETL.INP_REVENUEJ_LDS2013.

NOTE: The data set ETL.INP_ED_CT_LDS2013 has 340765 observations and 5 variables.

NOTE: DATA statement used (Total process time):

real time 3.73 seconds

cpu time 3.71 seconds

81 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: Data Step with BY and first.AAAA and last.BBBB not updating

PGStats — Mon, 02 Apr 2018 05:30:13 GMT

You are only missing the statement

retain ed_ct;

otherwise ed_ct is reset to missing on every datastep iteration.

You could also simplify to

if first.claim_no then ed_ct=0;

if rev_cntr in ('0450', '0451', '0452', '0456', '0459', '0981') then ed_ct = ed_ct + 1;

if last.claim_no then output;

Revise the way by-processing and output works.

Re: Data Step with BY and first.AAAA and last.BBBB not updating

Kurt_Bremser — Mon, 02 Apr 2018 11:59:10 GMT

If instead of

ed_ct = ed_ct + 1;

you would use

ed_ct + 1;

then SAS would automatically retain ed_ct.

Re: Data Step with BY and first.AAAA and last.BBBB not updating

fpascal — Mon, 02 Apr 2018 14:00:51 GMT

This solution worked and I will accept it. Can you please advise on the second part? The variable rev_cntr does not change. It stays at '0001'

Re: Data Step with BY and first.AAAA and last.BBBB not updating

fpascal — Mon, 02 Apr 2018 14:02:15 GMT

The simplified code did not work as written. Also there was a second part. The variable rev_cntr does not change. It stays at '0001'

Re: Data Step with BY and first.AAAA and last.BBBB not updating

Kurt_Bremser — Mon, 02 Apr 2018 14:17:48 GMT

@fpascal wrote:

This solution worked and I will accept it. Can you please advise on the second part? The variable rev_cntr does not change. It stays at '0001'

Why should rev_cntr change?

See your log example:

66 data etl.inp_ed_ct_lds2013 (drop=HCPCS_CD REV_CNTR_UNIT_CNT clm_thru_dt nch_clm_type_cd
67 REV_CNTR_RATE_AMT REV_CNTR_TOT_CHRG_AMT REV_CNTR_NCVRD_CHRG_AMT
68 REV_CNTR_DDCTBL_COINSRNC_CD REV_CNTR_APC_HIPPS_CD);
69 set etl.inp_revenuej_lds2013;
70 by desy_sort_key claim_no;
71
72 if first.desy_sort_key and first.claim_no then
73 ed_ct=0;
74
75 if rev_cntr in ('0450', '0451', '0452', '0456', '0459', '0981') then
76 ed_ct=ed_ct + 1;
77
78 if last.desy_sort_key and last.claim_no then output etl.inp_ed_ct_lds2013;
79 run;

rev_cnt is never set to a new value, so it stays as it came in from the input dataset. Inspect your dataset (etl.inp_revenuej_lds2013) see Maxim 3.

Re: Data Step with BY and first.AAAA and last.BBBB not updating

fpascal — Tue, 03 Apr 2018 03:38:31 GMT

Kurt - i am confused about your reply regarding rev_cntr. rev_cntr is a column in etl.inp_revenuej_lds2013. The value in the column is different for each value read in the SET statement. Please explain

Re: Data Step with BY and first.AAAA and last.BBBB not updating

Kurt_Bremser — Tue, 03 Apr 2018 06:48:05 GMT

@fpascal wrote:
Kurt - i am confused about your reply regarding rev_cntr. rev_cntr is a column in etl.inp_revenuej_lds2013. The value in the column is different for each value read in the SET statement. Please explain

For this to verify and test, we need data. Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert a reasonable part of your dataset to a data step and post that here, as described in https://communities.sas.com/t5/Getting-Started/How-to-add-SAS-syntax-to-your-post/ta-p/224394