DATA Step, Macro, Functions and more

To standardize data

Reply
Contributor
Posts: 37

To standardize data

Hi,

I have variables in file like

Name sex dob var1-var4 

 

Here i wanted to merge the multiple records into single record based on the name,dob,gender variables.

 


xx M 22/11/1992 2 0 7 0
xx M 22/11/1992 0 6 0 3
yy F 24/8/1989 2 0 6 0
yy F 24/8/1989 0 9 0 8
zz M 22/11/1992 2 0 7 0
zz M 22/11/1992 0 6 0 3

required

 

xx M 22/11/1992 2 6 7 3

yy F 24/8/1989 2 9 6 8

zz M 22/11/1992 2 6 7 3

Thanks 

Super User
Super User
Posts: 9,211

Re: To standardize data

Posted in reply to srinath3111

Post test data in the form of a datastep!!

 

You can do this in one proc summary/means step, for example:

proc means data=have;
  by name sex dob;
  var var:;
  output out=want sum=;
run;

Note note tested as I am not here to type test data in.

Super User
Posts: 13,066

Re: To standardize data

Posted in reply to srinath3111

@srinath3111 wrote:

Hi,

I have variables in file like

Name sex dob var1-var4 

 

Here i wanted to merge the multiple records into single record based on the name,dob,gender variables.

 


xx M 22/11/1992 2 0 7 0
xx M 22/11/1992 0 6 0 3
yy F 24/8/1989 2 0 6 0
yy F 24/8/1989 0 9 0 8
zz M 22/11/1992 2 0 7 0
zz M 22/11/1992 0 6 0 3

required

 

xx M 22/11/1992 2 6 7 3

yy F 24/8/1989 2 9 6 8

zz M 22/11/1992 2 6 7 3

Thanks 


Do you have any records that do not involve 0 in the "standardization" such as?

 

zz M 22/11/1992 2 3 7 8
zz M 22/11/1992 4 6 0 3

If so, what would the result for this look like?

Super Contributor
Posts: 472

Re: To standardize data

Posted in reply to srinath3111

Check this UPDATE statement:

data have;
infile datalines dlm=' ';
input Name $ sex $ dob ddmmyy10. var1-var4;
Array change _numeric_;
do over change;
if change=0 then change=.;
end;
datalines;
xx M 22/11/1992 2 0 7 0
xx M 22/11/1992 0 6 0 3
yy F 24/8/1989 2 0 6 0
yy F 24/8/1989 0 9 0 8
zz M 22/11/1992 2 0 7 0
zz M 22/11/1992 0 6 0 3
;
run;

proc sort data=have;
by name sex dob;
run;

data want;
update have(obs=0) have;
by name sex dob;
run;
Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 3 replies
  • 90 views
  • 0 likes
  • 4 in conversation