Reading Raw Hierarchical data files

[sleeks]

Ok my problem is I want to read a hierarchical raw data set like this one:

bobby 213
m1 5 7 9 10
colin 442
m1 7 3 10 5
m2 6 3 7 4
tracy 856
m2 7 8 6 7
cindy 365
m1 7 9 4 5
m2 3 9 8 4

and for SAS to output a result like this:

id name mark1 mark2 total
213 bobby 31 0 31
442 colin 25 20 45
856 tracy 0 28 28
365 cindy 25 24 49

mark1 is total of marks in m1
mark2 is total of marks in m2
total is equal to the sum of mark1 and mark2

this is my code so far:

proc fslist fileref='marks.txt';
run;
data marks (drop=Type q1 q2 q3 q4);
/* Drop inputs Type question1 = q1 - question4 = q4 */
retain id name q1 q2 q3 q4 mark1 mark2;
length id $3. name $10. Type $2. q1 3. q2 3. q3 3. q4 3.;
/* Keeping the length of all inputs for neatness */

infile 'marks.txt' end=LastRec;

input Type $@;

if Type='id' then do;
if _N_ > 1 then output;
input id $ name $; end;

else do;

input q1 q2 q3 q4;

if (Type = 'm1') and (Type ^= 'm2') and (Type ^= '.') then
mark1 = sum(q1,q2,q3,q4);
*total = mark1;

else if (Type ^= 'm1') and (Type = 'm2') and (Type ^= '.') then
mark2 = sum(q1,q2,q3,q4);
*total = mark2;

/*
if (Type = 'm1') and (Type ^= 'm2') then
mark1 = sum(q1,q2,q3,q4);
*/


/*
else if (Type = 'm2') and (Type ^= 'm1') then
mark2 = sum(q1,q2,q3,q4);
*/


/*
else if (Type = 'm1') and (Type = 'm2')
mark1 = sum(q1,q2,q3,q4);
mark2 = sum(q1,q2,q3,q4);
*/

end;
if LastRec then output;
run;
ods html file='marks.html';
proc print data=marks noobs;
title 'Marks';
run;
ods html close;


so far my output looks like this:

id name mark1 mark2
213 bobby 31 .
442 colin 25 20
856 tracy 25 28
365 cindy 25 24

as you can see id 856 should not have a mark for m1

Sorry for such a long question but I couldn't find any SAS forum on this topic.

Thanks to whoever can help

 

[sleeks]

Don't worry about it solved it already

 

[ChrisW75]

I guess it was the retain that tripped you up yes? The number of times I've done that...

There's a new CALL routine in SAS 9.1 which can help you to reset all your variables. A good practice with this sort of thing where you are retaining values over a number of input observations, is to empty all the values after outputting.
The new call routine is this:-

  call missing(var1,var2,var3.....varn);

This sets the values to missing (null), ready for the next iteration so you know that you aren't carrying anything over that you shouldn't be.
Enjoy.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.