creating the wide table from narrow table with Retain statement but not able to retain the values

Nikhilvg — Thu, 20 Aug 2020 03:07:14 GMT

Created the data in T1 and sorted by name variable in T2, and then wanted to create wide table with two columns Maths1 and English1. Wanted to output the when the last.name=1 so that Maths1 and English1 variable value will come in one row. But the values in PDV not retaining for variables. Please help.

Expected output:

Name Maths1 English1

Nikhil 45 12

gho 48 78

monu 56 43

sonu 54 86

veena 66 23

Program Code;

data T1;
infile datalines;
length Name Subject = $10;
input Name $ Subject$ Marks;
datalines;
Nikhil Maths 45
Nikhil English 12
gho Maths 48
gho English 78
veena Maths 66
veena English 23
sonu Maths 54
sonu English 86
monu Maths 56
monu English 43
;
run;

Proc print data=T1;
run;
proc sort data=T1 out=T2;
by Name;
run;
Proc print data=T2;
run;

data T3;
set T2;
by Name;
retain Name Subject Marks;
if Subject="Maths" then
Maths1=Marks;
else if Subject="English" then
English1=Marks;
if last.name=1 then output;
run;

proc print data=T3;
run;

Re: creating the wide table from narrow table with Retain statement but not able to retain the value

ballardw — Thu, 20 Aug 2020 03:13:55 GMT

You are retaining the wrong variables. You used

retain Name Subject Marks;

Try

retain maths1 english1;

You may also want: KEEP name maths1 english1; so those are the only values kept in the output data set.

If you Retain a variable that is in the data set used on a SET statement the value in the data set is reset for each record read.

topic creating the wide table from narrow table with Retain statement but not able to retain the values in SAS Programming

creating the wide table from narrow table with Retain statement but not able to retain the values

Re: creating the wide table from narrow table with Retain statement but not able to retain the value