topic Re: Assign value for counting in SAS Programming

Assign value for counting

JH74 — Wed, 26 Apr 2023 16:59:28 GMT

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.

Re: Assign value for counting

Reeza — Wed, 26 Apr 2023 17:08:30 GMT

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.

Re: Assign value for counting

ballardw — Wed, 26 Apr 2023 17:09:03 GMT

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