Hi. I'm trying to create a new variable for later summing to get a total count for unique conditions.
My data looks like this:
ID | from_dt | to_dt |
123 | 06JAN2021 | 06JAN2021 |
123 | 13JAN2021 | 13JAN2021 |
123 | 20JAN2021 | 20JAN2021 |
123 | 27JAN2021 | 27JAN2021 |
456 | 03FEB2021 | 03FEB2021 |
456 | 04FEB2021 | 04FEB2021 |
789 | 06MAR2021 | 06MAR2021 |
789 | 06MAR2021 | 06MAR2021 |
789 | 06MAR2021 | 06MAR2021 |
My code is this:
data services_test;
set services;
by id from_dt to_dt ;
if first.id and first.from_dt and first.to_dt then visits = 1;
run;
It produces this:
ID | from_dt | to_dt | Visits |
123 | 06JAN2021 | 06JAN2021 | 1 |
123 | 13JAN2021 | 13JAN2021 | . |
123 | 20JAN2021 | 20JAN2021 | . |
123 | 27JAN2021 | 27JAN2021 | . |
456 | 03FEB2021 | 03FEB2021 | 1 |
456 | 04FEB2021 | 04FEB2021 | . |
789 | 06MAR2021 | 06MAR2021 | 1 |
789 | 06MAR2021 | 06MAR2021 | . |
789 | 06MAR2021 | 06MAR2021 | . |
I understand why I'm getting the output I am, but I'm trying to get this result:
ID | from_dt | to_dt | Visits |
123 | 06JAN2021 | 06JAN2021 | 1 |
123 | 13JAN2021 | 13JAN2021 | 1 |
123 | 20JAN2021 | 20JAN2021 | 1 |
123 | 27JAN2021 | 27JAN2021 | 1 |
456 | 03FEB2021 | 03FEB2021 | 1 |
456 | 04FEB2021 | 04FEB2021 | 1 |
789 | 06MAR2021 | 06MAR2021 | 1 |
789 | 06MAR2021 | 06MAR2021 | . |
789 | 06MAR2021 | 06MAR2021 | . |
I need a visits = 1 for each ID that has a different from/to dates, but I only need a visits = 1 when the ID has the same from/to dates.
data services_test;
set services;
by id from_dt to_dt ;
if first.id or first.from_dt then visits=1;
run;
Not 100% sure this will scale to your full requirements.
data services_test;
set services;
by id from_dt to_dt ;
if first.id or first.from_dt then visits=1;
run;
Not 100% sure this will scale to your full requirements.
See if this does what you want:
data services_test; set services; by id from_dt to_dt ; if first.from_dt and first.to_dt then visits = 1; run;
You really need to consider providing examples with a bit more complexity. All of your From and To dates are identical. So I can't really be sure that the First.to_dt is even needed.
When you have multiple By variables you get combinations of the First and Last variables. so you were, by requiring First.ID excluding any result for other than the first observation of each Id.
In some complex needs for mixes of First and Last variable I find it helpful sometimes to ADD the values so I can see if my logic is correct.
data services_test; set services; by id from_dt to_dt ; F_id = first.id; F_From = first.from_dt; F_to = first.to_dt; run;
and look at those (and sometimes the Last versions as well).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: