Roll up two rows into one

[sivaranjani]

Hi,

I am trying to roll up multiple rows into one row. I am not sure how to achieve that.

I want table 1 to look like table 2. One row per student. 

Thanks!

[PaigeMiller]

First, you can't have two different columns in a SAS data set named SUBJECT1.

Next, this is rarely a good idea to convert long data sets to wide, as you are trying to do, as most forms of analysis in SAS are better performed with the long data sets rather than the wide. Although you say you would like to have a wide format, could you explain why this is needed? If the need is to create a report, then PROC REPORT will do this without first converting the data set from long to wide.

[sivaranjani]

Hi Miller,

I will be changing the column names, so that wouldn't cause any issues. I need a wide report because I will be doing further manipulation and analysis on the data. Long report will not work for my requirement.

[PaigeMiller]

---

@sivaranjani wrote:

Hi Miller,

I will be changing the column names, so that wouldn't cause any issues. I need a wide report because I will be doing further manipulation and analysis on the data. Long report will not work for my requirement.

---

So, maybe I should just drop the subject, but sometimes people think it is better to work with the wide format, and they are mistaken. There are very few things that work better with a wide format, statistical modelling one of them. Simply computing means and other descriptive statistics (and computing them by semester) work much better in the long format.

[SASKiwi]

@sivaranjani  - What are your requirements? If you explained this, then we can help you choose the best data design for your needs.

[jebjur]

Here is a sample program that uses dummy data to illustrate how to collapse data into a single observation within a BY group. 

data sample;
input repID IndID name $;
cards;
5424 19080134 Tom
5424 19080135 Jon
5424 19080136 Bob
5425 19081134 Ken
5425 19081135 Matt
5425 19081136 Rick
;
run;

data collapse (drop=i j IndID name);
set sample;
by repID;
retain name1-name3 id1-id3;
array names (3) $ name1-name3;
array ids (3) id1-id3;
if first.repID then do;
 i=1;
  do j=1 to 3;
  names(j)=' ';
  ids(j)=.;
end;
end;

names(i)=name;
ids(i)=IndID;

if last.repID then output;
i+1;
run;

[ballardw]

Do you want a data set for manipulation or a report that people will read?

A similar, and I think easier to read, report could come from Proc Report with Semester as an Across variable with the other variables nested below.

I may be confused. In a context with names and grades would typically be something like Math, History, Art and so on. Do ALL of these apparent students only take the same three classes? Or are you hiding the fact that some students have 5 subjects and others only 2 or different subjects entirely?

[Ksharp]

data sample;
input repID IndID name $;
cards;
5424 19080134 Tom
5424 19080135 Jon
5424 19080136 Bob
5425 19081134 Ken
5425 19081135 Matt
5425 19081136 Rick
;
run;

proc sql noprint;
 select max(n) into : n
  from (select count(*) as n from sample group by repID);
quit;
proc summary data=sample;
by repID;
output out=want idgroup(out[&n] (IndID name)=);
run;

[W_liu]

Good idea(I hope I didn't offend you):I tried to imitate it

data a;

      input id name $7-12 semester subject1 subject2 subject3 grade;

cards;

22345    Alex  1 90 87 93 4.6

22345    Alex  2 84 75 96 4.1

26534 James 1 76 89 73 3.8

26534 James 2  0    0   0    0

45663 Mervin 1 78 74 80    3

45663 Mervin 2 80 84 79 3.7

;

run;

proc sql noprint;

        select max(m) into :m

        from (select count(*) as m from a group by id,name);

quit;

%put &m;

proc summary data=a;

        by id name;

        output out=b idgroup(out[&m](semester subject1 subject2 subject3 grade)=);

run;

data c;

       retain id name semester_1 subject1_1 subject2_1 subject3_1 grade_1 semester_2 subject1_2 subject2_2 subject3_2 grade_2;

       set b;

      drop _type_ _freq_;

run;

[sivaranjani]

Thank you! this is working, but I am facing a little issue here.

When a student has no data for Semester 1 but has data for Semester 2, the semester 2 data is filled in place of semester 1 and semester 2 has null values. How do I make sure that the data goes into the right semester?