BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fpascal
Quartz | Level 8

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):(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
 
 
80
81 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
94
 
1 ACCEPTED SOLUTION
7 REPLIES 7
PGStats
Opal | Level 21

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
fpascal
Quartz | Level 8
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'
fpascal
Quartz | Level 8

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'

Kurt_Bremser
Super User

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

 
fpascal
Quartz | Level 8
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
Kurt_Bremser
Super User

@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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