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

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

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

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;
61
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
 
 
65
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;
 
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)Smiley SadColumn).
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
 
 
80
81 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
94
 

Accepted Solutions
Solution
‎04-02-2018 11:08 PM
Super User
Posts: 10,258

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

If instead of

ed_ct = ed_ct + 1;

you would use

ed_ct + 1;

then SAS would automatically retain ed_ct.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,531

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

[ Edited ]

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.

PG
Contributor
Posts: 44

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

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'
Solution
‎04-02-2018 11:08 PM
Super User
Posts: 10,258

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

If instead of

ed_ct = ed_ct + 1;

you would use

ed_ct + 1;

then SAS would automatically retain ed_ct.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 44

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

Posted in reply to KurtBremser

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'

Super User
Posts: 10,258

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


@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.

 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 44

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

Posted in reply to KurtBremser
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
Super User
Posts: 10,258

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


@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-dat... 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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 166 views
  • 1 like
  • 3 in conversation