topic Re: New dataset with Unique Reference Numbers in SAS Programming

New dataset with Unique Reference Numbers

Haydn — Thu, 24 Nov 2016 00:14:23 GMT

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)
REF	Childage
123	1
123	7
124	10
125	10
125	16

Expected outcome (new datset called Child)
REF	0 to 4	5 to 9	10 to 14	15 to 19
123	1	1	0	0
124	0	0	1	0
125	0	0	1	1

Re: New dataset with Unique Reference Numbers

Astounding — Thu, 24 Nov 2016 00:56:05 GMT

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.

Re: New dataset with Unique Reference Numbers

Jagadishkatam — Thu, 24 Nov 2016 00:56:57 GMT

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;

Re: New dataset with Unique Reference Numbers

Haydn — Thu, 24 Nov 2016 01:30:34 GMT

thanks guys, your help is much appreciated.

Cheers

Haydn

Re: New dataset with Unique Reference Numbers

keane76 — Thu, 24 Nov 2016 06:21:37 GMT

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;