BookmarkSubscribeRSS Feed
sivaranjani
Fluorite | Level 6

 

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. sample data.PNG

 

 

Thanks!

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
sivaranjani
Fluorite | Level 6

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
Diamond | Level 26

@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.

--
Paige Miller
SASKiwi
PROC Star

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

jebjur
SAS Employee

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
Super User

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
Super User

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
Calcite | Level 5

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
Fluorite | Level 6

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?

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1761 views
  • 1 like
  • 7 in conversation