topic Re: Create a variable a new variable based on two other variables when groups are small in SAS Procedures

Create a variable a new variable based on two other variables when groups are small

ANKH1 — Mon, 27 Feb 2023 17:23:29 GMT

We have this dataset. There are only two items from the same store. And we want to determine items as new/old within the same store based on the year.

ID	YEAR	STORE
100	2001	1
101	2003	1
102	1999	2
103	2004	2
104	2005	3
105	2007	3

This is the desired output

ID	YEAR	STORE	OLD_NEW
100	2001	1	OLD
101	2003	1	NEW
102	1999	2	OLD
103	2004	2	NEW
104	2005	3	OLD
105	2007	3	NEW

Re: Create a variable a new variable based on two other variables when groups are small

Reeza — Mon, 27 Feb 2023 17:42:18 GMT

What determines an "item"? ID/Year are Unique and Store repeats twice each, I'm not sure your sample data adequately illustrates your problem. Can you add some more details about what you're trying to accomplish?

You can get the output shown by using FIRST/LAST but I don't think it will work with your actual data.

data want;
set have;
by store year;
if first.store then old_new = 'OLD';
if last.Store then old_new = 'NEW';
run;

Re: Create a variable a new variable based on two other variables when groups are small

ANKH1 — Mon, 27 Feb 2023 17:52:13 GMT

Yes, ID is each item and year is year of manufacturing. We want to determine within the same store, which item is older based on when manufacturing date (year).

Re: Create a variable a new variable based on two other variables when groups are small

vijaypratap0195 — Fri, 21 Apr 2023 17:52:41 GMT

To get the desired result use the proc sort procedure, then use first.store to get the new_items else old_items.

Please refer to code-

proc sort data=dt; by id year store; run;
proc sort data=dt; by store; run;

data dt1;
set dt;
by store;
if first.store then old_new = "New";
else old_new= "old";
run;

-Vijay