topic Re: How do I flag increases in variables that are in two datasets in SAS Programming

How do I flag increases in variables that are in two datasets

xxartpopxx — Fri, 17 Feb 2023 19:27:04 GMT

I have two different datasets. One from the week prior and one for this week looking at various variables such as gender, sex, race and age (all categorical variables). I have joined the two datasets in the first step below in a proc sql (table joined). This creates old and new variables from each dataset. I am stuck on how to create a flag variable for significant increases/decreases of each variable in a proc freq that I can output. I want to know if any of the gender_new categories (current week) increased/decreased by 100+ from gender_old categories (week prior).

proc sql;
create table joined as
select coalesce(a.person_key, b.person_key) as person_key,
a.gender_old, b.gender_new,
a.sexor_old, b.sexor_new,
a.race_cd_old, b.race_cd_new,
a.age_group_old, b.age_group_new,
a.sex_old, b.sex_new,
a.peh1_old, b.peh1_new,
a.peh2_old, b.peh2_new,
a.dose_total_old, b.dose_total_new
from oldperson a
full join newperson b
on a.person_key = b.person_key;
quit;

/Create Flag Variable/

Flag significant increases and flag ANY drops. o Flag increase of 100+ for all metrics o Flag any drops for all metrics

Re: How do I flag increases in variables that are in two datasets

PaigeMiller — Fri, 17 Feb 2023 19:35:09 GMT

PROC FREQ will give you the counts of Gender_New and Gender_Old. You can ask PROC FREQ to create SAS data sets for each with the counts. Then you merge the two data sets, do a subtraction and if the difference is less than 0 or greater than 100, you set a flag.

Joining the two data sets doesn't even seem like a necessary step.

Re: How do I flag increases in variables that are in two datasets

xxartpopxx — Fri, 17 Feb 2023 19:38:49 GMT

Hi Paige

variables gender_old and gender_new for instance are already in the joined table.

what would the datastep look like to flag the differences between these

Re: How do I flag increases in variables that are in two datasets

PaigeMiller — Fri, 17 Feb 2023 19:53:14 GMT

@xxartpopxx wrote:

Hi Paige

variables gender_old and gender_new for instance are already in the joined table.

what would the datastep look like to flag the differences between these

It looks like a data step where you merge the two outputs from PROC FREQ, do a subtraction, and then and IF statement to determine if you have >100 or <0

Re: How do I flag increases in variables that are in two datasets

Patrick — Sat, 18 Feb 2023 00:50:48 GMT

@xxartpopxx If you provide representative sample data via a working SAS data step that we just can copy/paste to create the data, show us the desired outcome and explain the logic you have in mind to get from have to want then there is a good chance that someone will answer your question by providing working SAS code.