topic Re: Update one dataset with same value for selected field for only ids in another dataset in SAS Programming

Update one dataset with same value for selected field for only ids in another dataset

jcis7 — Mon, 18 Apr 2022 22:28:27 GMT

G'day!

Appreciate any help you can give.

I've two datasets with the following names and content:

'idsdataset' that has the variable name 'id' and 'type'
'toupdatedataset' that has the variable names 'id', 'type', and 'status'

I'd like to update the field 'status' in the dataset 'toupdatedataset' with the value 'N' for all of the observations in the 'idsdataset' dataset based on 'id' in both datasets.

The outcome I'm looking for is listed in the dataset 'updateddataset'.

How can I update 'status' field without needing to enter each 'id' individually? Thank you.


data idsdataset;
   input id $ type$;
   datalines;
1234567 Pub
1245678 Pub
;
data toupdatedataset;
   input id $ type $ status $;
   datalines;
1234567 Pub .
1245678  Pub . 
2349878 Pri N
3948573 Priv Y
;

data updateddataset;
  set toupdate;
if id in ('1234567' , '1245678') then status='N';
run;

Re: Update one dataset with same value for selected field for only ids in another dataset

ballardw — Mon, 18 Apr 2022 23:40:57 GMT

Here is one way:

data updateddataset;
  merge toupdatedataset (in=Inleft) 
        idsdataset      (in=Inright)
  ;
  by id;
  if missing(status) and inleft and inright then Status='N';
run;

If the data sets on the Merge statement are not sorted by ID then you should sort them prior to merge.

The dataset option IN= creates a temporary variable (means that variable is not written to the data) that is a 1/0 (True/False) value. So you can test if a record is coming from both data sets. I am assuming that you want to change the missing status values if present only. If that is not actually the case then remove the "missing(status) and" part of the If.

Please make sure that you spell your data sets consistently. I could not tell if there was supposed to be a 4th data set named Toupdate that was actually different than Toupdatedataset.

Warning about the above solution: If your ISDATASET contains ID and TYPE not already in the TOUPDATEDATASET they will be added (and status will be missing). If this might occur and you do not want it to then you would subset the data with an "IF inleft;"

Re: Update one dataset with same value for selected field for only ids in another dataset

HB — Mon, 18 Apr 2022 22:59:59 GMT

I like Ballard's way better (mine is the long way around) but I'll post this anyway:



*populate the missings;
proc sql;
create table populatedidsdataset as 
select a.id, a.type,
case
	when missing(status) then 'N'
	else status 
end as status
from
toupdatedataset a inner join idsdataset b
on a.id = b.id and a.type = b.type;
quit; 

* keep your good ones;
data parsed;
set toupdatedataset;
if not missing(status);
run;

* smash your populated ones and good ones back together;
data want;
	merge populatedidsdataset parsed;
	by id; 
run;

Re: Update one dataset with same value for selected field for only ids in another dataset

jcis7 — Mon, 18 Apr 2022 23:03:45 GMT

Thanks for catching that I didn't name the dataset in the set statement in the 'data updateddataset' statement 'toupdatedataset' and instead named it 'toupdate'.

Re: Update one dataset with same value for selected field for only ids in another dataset

Sajid01 — Mon, 18 Apr 2022 23:25:56 GMT

Hello @jcis7
As I understand from your post, the requirement is that if the id's in both the datasets ( idsdataset and toupdatedataset ) match then set the values of Status to N in the updated dataset . Thus I would go by what @ballardw has given with a minor change. My code would be as follows

data idsdataset;
   input id $ type$;
   datalines;
1234567 Pub
1245678 Pub
;
data toupdatedataset;
   input id $ type $ status $;
   datalines;
1234567 Pub .
1245678  Pub . 
2349878 Pri N
3948573 Priv Y
;
proc sort data=idsdataset;
by id;
run;
proc sort data=toupdatedataset;
by id;
run;
data updateddataset;
  merge toupdatedataset (in=Inleft) 
        idsdataset      (in=Inright)
  ;
  by id;
  if inleft and inright then Status='N';
run;

Re: Update one dataset with same value for selected field for only ids in another dataset

jcis7 — Mon, 25 Apr 2022 22:30:34 GMT

Thank you everyone!