BookmarkSubscribeRSS Feed
CathyVI
Pyrite | Level 9

I am trying to find the “number and percentage of distribution of encounter with at least one medication record by data source”. This is my sample data and my sql code but I am not convinced, any help/suggestion please, Thanks.

 

Source

Hosp_id

Encounter_id

Medication_code

Medication_name

ccd

1123

3014759650x

1234

aspirin

ccd

1123

3014759650x

5678

ibuprofen

ehr

1123

3014751234x

9876

Tylenol

ehr

1222

3014751234x

1234

aspirin

ehr

1222

3014751234x

9876

Tylenol

 

For my code, I said medi_name =1, when medication_name is present, else medi_name=0

                                 medi_code =1, when medication_code is present, else medi_code=0.

proc sql;

create table tm.testrun as

select source, count(source)as s_sum, encounter_id, count(encounter_id)as e_sum

from Tm.med

where medi_name = 1 and medi_code =1;

quit;

 

proc freq data= Tm.testrun;

tables source;

run;

5 REPLIES 5
Kurt_Bremser
Super User

Please supply example data in usable way (as @PeterClemmensen showed you in https://communities.sas.com/t5/SAS-Programming/Help-with-Summary-Analysis/m-p/564236#M158244). Then also supply the code that created medi_name and medi_code, just to be sure.

And finally, show what you expect as output from the example data as posted.

CathyVI
Pyrite | Level 9

Please see data in useable way;

data test;

input source $ 1-3 Hosp_id 5-8 encounter_id $ 10-20 medication_code $ 22-25 medication_name $ 26-35;

datalines;

ccd 1123 3014759650x 1234 aspirin

ehr 1123 3014759650x 5678 ibuprofen

ehr 1123 3014751234x 9876 tylenol

ehr 1222 3014751234x 1234 aspirin

ccd 1222 3014751234x 9876 tylenol

;

run;

 

this is the code I used to create medi_name and medi_code;

 

data Tmp.drug(compress=Yes);

set t.test keep=source hosp_id encounter_id  medication_code medication_name);

if medication_code = "" then medi_code = 0;

else medi_code = 1;

if medication_name = "" then medi_name = 0;

else medi_name= 1;

run;

Kurt_Bremser
Super User

I have combined all the code you provided into one piece of working code:

data test;
input source $ 1-3 Hosp_id 5-8 encounter_id $ 10-20 medication_code $ 22-25 medication_name $ 26-35;
datalines;
ccd 1123 3014759650x 1234 aspirin
ehr 1123 3014759650x 5678 ibuprofen
ehr 1123 3014751234x 9876 tylenol
ehr 1222 3014751234x 1234 aspirin
ccd 1222 3014751234x 9876 tylenol
;

data drug;
set test (keep=source hosp_id encounter_id  medication_code medication_name);
medi_code = (medication_code > "");
medi_name = (medication_name > "");
run;

proc sql;
create table testrun as
select source, count(source)as s_sum, encounter_id, count(encounter_id)as e_sum
from drug
where medi_name = 1 and medi_code =1;
quit;

Note that

  • I simplified the creation of medi_code and medi_name, but the result is the same
  • all observations have a medi_name and medi_code of 1. You may need to overthink the logic for building those.
  • the SQL does a remerge, because there is no group by clause, so the sums are all the same

You did not tell us what you expect to get out of your example dataset. Given its small size, it should not be difficult to create this "by hand".

gamotte
Rhodochrosite | Level 12

Hello,

 

Not tested as you did not provide data in a usable form :

 

proc sql;
    create table tm.testrun as
    select distinct source, count(source)as s_sum, encounter_id, count(encounter_id)as e_sum
    from Tm.med
    group by source
    having sum(Medication_code ne " ")>0 and sum(Medication_name ne " ")>0;
quit;
CathyVI
Pyrite | Level 9

I tested your code and I got the expected result, Thank you!

 

So I have two questions.

 

1. Can I add another variable to the initial code you answered. I mean if I want to "Number and percent encounters with atleast One Med record by setting_id and source"

 

2. Can I used the same code to find another variable in the same dataset. See code below.

Am looking for "Number and percentage of encounters by "medication_code" and "setting_id".

I added medication_code to the code you created but I removed the "distinct" since am not looking for atleast one record.

 

proc sql;

create table tm.testrun as

select source, setting_id, count(medication_code)as med_sumv, medication_code, encounter_id, count(encounter_id)as e_sum

from Tm.med

group by medication_code, setting_id

having sum(Medication_code ne " ")>0 and sum(Medication_name ne " ")>0;

quit;

 

proc freq data= Tm.testrun;

tables medication_code*setting_id/crosslist;

run;

 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1786 views
  • 0 likes
  • 3 in conversation