BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DeepakSwain
Pyrite | Level 9

Hi there,

 

I am having a data consisting of multiple transactions for a person within a same day but some of the records for the same person is missing e.g site ID. I want to fill the missing site ID by taking value from one of the site ID from the transactions of that person on the same date assuming that in a day all transaction occurs in one site only. Can some one kindly help me to move forward. 

data have;
input name $ service_date site_ID ;
cards;
aaa 20140102 101
aaa 20140102    
bbb 20140102 102
ccc 20140104 103
ccc 20140104    
ccc 20140104 103
aaa 20140105 104
aaa 20140105    
aaa 20140105 104
;
run;




data want;
input name $ service_date site_ID ;
cards;
aaa 20140102 101
aaa 20140102 101
bbb 20140102 102
ccc 20140104 103
ccc 20140104 103
ccc 20140104 103
aaa 20140105 104
aaa 20140105 104
aaa 20140105 104
;
run;

 

Thank you in advance for your kind reply. 

 

Regards,

 

Swain
1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

Please check the site_id2 variable with expected result

 

proc sort data=have;
by name service_date;
run;

data want;
set have;
retain site_id2;
by name service_date;
if first.service_date then site_id2=site_id;
run;
Thanks,
Jag

View solution in original post

10 REPLIES 10
Jagadishkatam
Amethyst | Level 16

Please check the site_id2 variable with expected result

 

proc sort data=have;
by name service_date;
run;

data want;
set have;
retain site_id2;
by name service_date;
if first.service_date then site_id2=site_id;
run;
Thanks,
Jag
DeepakSwain
Pyrite | Level 9
Hi Jagadish,
Thanks for your quick reply.
Regards,
Swain
mkeintz
PROC Star

What if the first record for a given date has a missing site?  This program would propagate that missing value to all records for the given date.

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
DeepakSwain
Pyrite | Level 9

Hi Mkeintz

 

Swain
Jagadishkatam
Amethyst | Level 16

for your query, i considered the scenario you mentioned. We could avoid the same by changing the sorting order of the site_id, we need to ensure that the non missing site_id comes first and the same could be retained to other records. This time I used the update statement to achieve the expected output along with the proc sort step.

 

data have;
infile cards missover;
input name $ @5 service_date site_ID ;
cards;
aaa 20140102    
aaa 20140102 101
bbb 20140102 102
ccc 20140104 103
ccc 20140104    
ccc 20140104 103
aaa 20140105 104
aaa 20140105    
aaa 20140105 104
;
run;

proc sort data=have;
by name service_date descending site_ID;
run;

data want;
update have(obs=0) have;
by name service_date descending site_ID;
output;
run;
Thanks,
Jag
DeepakSwain
Pyrite | Level 9

Hi Jag,

 

Once again thank you for your superfast incredible reply. Just extending the "sorting part" of the solution advised by you, helped to achieve the expected solution using retain statement as before.

 

In order to increase my knowledge base, I am curious to know the solution based on update statement. Can you review the update statement part as following its use, I am not getting expected result.  

 

Thank you in advance for your kind reply. 

 

Regards,

Swain
Jagadishkatam
Amethyst | Level 16
sorry a small change, please try the below code
remove the descending site_id from update statement code.

data want;
update have(obs=0) have;
by name service_date ;
output;
run;
Thanks,
Jag
DeepakSwain
Pyrite | Level 9

Hi Jag,

 

Thank you for your kind patience in replying my queries. Your advice worked to provide me expected outcome. 

 

Regards,

 

Swain
mkeintz
PROC Star

Why not keep the basic structure of the program, but read the data in two parallel streams, one for site_id missing and one for the rest?

 

data want;
  set have (where=(site_id^=.)) have (where=(site_id=.));
  retain site_id2;
  by name service_date;
if first.service_date then site_id2=site_id;
run;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
DeepakSwain
Pyrite | Level 9

Hi 

 

 

Swain

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1593 views
  • 6 likes
  • 3 in conversation