DATA Step, Macro, Functions and more

New dataset with Unique Reference Numbers

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

New dataset with Unique Reference Numbers

Hi All,

 

I had a dataset called Child. This dataset has a field called 'REF', this is the parents reference number. It also has a field called 'Childage'.

 

The parent can have more than one child, in which case the same REF can be listed multiple times.

 

What I am after is to only have unique REF and new fields created called 0 to 4, 5 to 9,10 to 14 and 15 to 19, which represents the child's age in a grouping.

 

 

Current Data (Child)  
REFChildage   
1231   
1237   
12410   
12510   
12516   
     
Expected outcome (new datset called Child)
REF0 to 45 to 910 to 1415 to 19
1231100
1240010
1250011

 

 

 


Accepted Solutions
Solution
‎11-23-2016 08:26 PM
Super User
Posts: 5,085

Re: New dataset with Unique Reference Numbers

You have some decisions to make, since your new column headings are not legitimate names for variables in SAS.  Assuming that you still want a data set (and not just a report), here's one way to program it:

 

data want;

set have;

by ref;

if first.ref then do;

    _0_to_4 = 0;

    _5_to_9 = 0;

    _10_to_14 = 0;

    _15_to_19 = 0;

end;

if (0 <= ChildAge <= 4) then _0_to_4 + 1;

else if (5 <= ChildAge <= 9) then _5_to_9 + 1;

else if (10 <= ChildAge <= 14) then _10_to_14 + 1;

else if (15 <= ChildAge <= 19) then _15_to_19 + 1;

if last.ref;

run;

 

There are other ways, but this is one of the more straightforward approaches.

View solution in original post


All Replies
Solution
‎11-23-2016 08:26 PM
Super User
Posts: 5,085

Re: New dataset with Unique Reference Numbers

You have some decisions to make, since your new column headings are not legitimate names for variables in SAS.  Assuming that you still want a data set (and not just a report), here's one way to program it:

 

data want;

set have;

by ref;

if first.ref then do;

    _0_to_4 = 0;

    _5_to_9 = 0;

    _10_to_14 = 0;

    _15_to_19 = 0;

end;

if (0 <= ChildAge <= 4) then _0_to_4 + 1;

else if (5 <= ChildAge <= 9) then _5_to_9 + 1;

else if (10 <= ChildAge <= 14) then _10_to_14 + 1;

else if (15 <= ChildAge <= 19) then _15_to_19 + 1;

if last.ref;

run;

 

There are other ways, but this is one of the more straightforward approaches.

Trusted Advisor
Posts: 1,131

Re: New dataset with Unique Reference Numbers

please try the below code

 

data have ;
input REF Childage;
cards;	 	 	 
123 1	 	 	 
123 7	 	 	 
124 10	 	 	 
125 10	 	 	 
125 16
;

/*Create the formats*/
proc format ;
value range
low-4='0 to 4'
5-9='5 to 9'
10-14='10 to 14'
15-19='15 to 19';
run;

/*apply the format*/
data want;
set have;
x=put(childage,range.) ;
run;

/*option missing will make the blank variable values zero and validvarname will allow the variable names to start with numeric
and proc tranpose will transpose the data*/ options missing=0 validvarname=any; proc transpose data=want out=want_ ; by ref; var childage; id x; run;
Thanks,
Jag
Contributor
Posts: 37

Re: New dataset with Unique Reference Numbers

thanks guys, your help is much appreciated.

 

Cheers

Haydn

Occasional Contributor
Posts: 16

Re: New dataset with Unique Reference Numbers

data p;

input ref chage;

datalines;

123 4

123 10

678 9

678 8

789 2

789 5

;

run;

data new;

length age_class $10;

set p;

if chage< 5 then age_class='0 to 4';

else if chage< 10 then age_class='5 to 9';

else if chage< 15 then age_class='10 to 14';

else if chage< 20 then age_class='15 to 19';

run;

proc sort data=new;

by age_class;

run;

proc freq data=new;

by age_class;

tables ref * chage/nocol nocum nopercent norow;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 199 views
  • 2 likes
  • 4 in conversation