topic Re: getting multiple rows of dates for a subject into a single row in SAS Programming

getting multiple rows of dates for a subject into a single row

Kc2 — Thu, 14 Jan 2016 22:35:33 GMT

Hello,

I have a dataset where I have multiple dates per subject on different rows.

I would like to have all the dates into one row. Each row has a number of other parameters.

have data look like this

subject startdt enddt topic comments location

1 1jan2015 31mar2015 english good buildA

1 1apr2015 30jun2015 science average LabB

2 1jan2015 28feb2015 math bad mathlab

3 1jan2015 28feb2015 math good mathlab

3 1mar2015 31mar2015 history good buildA

3 1apr2015 30jun2015 science average LabB

4 1mar2015 31mar2015 history good buildA

5 1mar2015 31mar2015 english good buildA

5 1apr2015 30jun2015 science average LabB

I would like the data to look like

subject startdt1 enddt1 topic1 comments1 location1 startdt2 enddt2 topic2 comments2 location1 startdt3 enddt3 topic3 comments3 location3

1 1jan2015 31mar2015 english good buildA 1apr2015 30jun2015 science average LabB

2 1jan2015 28feb2015 math bad mathlab

3 1jan2015 28feb2015 math good mathlab 1mar2015 31mar2015 history good buildA 1apr2015 30jun2015 science average LabB

4 1mar2015 31mar2015 history good buildA

5 1mar2015 31mar2015 english good buildA 1apr2015 30jun2015 science average LabB

Thanks fo your help.

Re: getting multiple rows of dates for a subject into a single row

Reeza — Thu, 14 Jan 2016 22:40:03 GMT

I would highly recommend you don't. As is, your data can be analyzed nicely to produc summary statistics or answer queries.

If you change the format it becomes harder.

Ie common questions:

How many students took english?

How many students took english and math together?

How many students took english and liked the course?

If you change the format, answering each of the question above becomes much harder than it currently is.

What type of analysis/reporting are you doing, where you think this would be helpful.

Re: getting multiple rows of dates for a subject into a single row

ballardw — Thu, 14 Jan 2016 23:22:23 GMT

An example of what @Reeza mentions:

proc print data=have;
   title "Enrolled in Feb";
   where month(startdt) le 2 le month(enddt);
   var subject startdt enddt topic;
run;title;

Will get a list of students and classes enrolled in Februrary. If you have the data on one line then you would have to search each pair of date values. And considering that one subjec could well be enrolled in more than 2 classes over any period of time you could end up with something so unmangeable (think 8 semesters and maybe 50+ classes within that time frame).

Re: getting multiple rows of dates for a subject into a single row

Kc2 — Thu, 14 Jan 2016 23:31:12 GMT

Hi Reeza,

this is a mock dataset i created. The actual one I am working on is for a drug analyis. where the different dates corresponds to period of intake.

So I want to have all the intake period in one row and also their associated side effects.

I cannot post the actual dataset, evne a small sample to I created a mock one.

it would be something liek;

id, prd1st ,prd1end ,sideffect in prd1, prd2st ,prd2end, side effect in prd2.

Re: getting multiple rows of dates for a subject into a single row

Reeza — Thu, 14 Jan 2016 23:34:47 GMT

Since you're probably going to anyways, here's a macro that helps transpose the data.

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

Re: getting multiple rows of dates for a subject into a single row

Ksharp — Fri, 15 Jan 2016 01:09:43 GMT

The simplest way to do this is using proc means + idgroup .

Or if you care about efficiency and more function, you can consider the MERGE skill ,me Matt and Arthur.T proposed .

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
input subject      (startdt             enddt                           topic               comments           location) (: $20.);
cards;  
1               1jan2015         31mar2015                 english             good                      buildA
1               1apr2015         30jun2015                 science             average                  LabB
2               1jan2015         28feb2015                  math                  bad                         mathlab
3               1jan2015         28feb2015                  math                good                        mathlab 
3               1mar2015        31mar2015              history                good                        buildA
3               1apr2015         30jun2015                 science             average                  LabB
4              1mar2015        31mar2015              history                good                        buildA
5               1mar2015        31mar2015              english                good                        buildA
5               1apr2015         30jun2015                 science             average                  LabB
;
run;
proc sql;
select max(n) into : n
 from (select count(*) as n from have group by subject );
quit;
proc summary data=have;
by subject;
output out=want idgroup(out[&n] (startdt   enddt     topic   comments location)=);
run;

Re: getting multiple rows of dates for a subject into a single row

Kc2 — Fri, 15 Jan 2016 02:19:03 GMT

Before posting on the forum , I had try 2 methods;

method1

proc sort with dupout option followed by a merge in a data step

proc sort data =have out= stdat dupout=endat nodupkey ; by id;run;

data want;

merge stdat endat;

by id;

run;

it worked except for the case where the id has 3 dates.

method 2

Then I tried a second method; proc sort by id and start date and then using a data step with the first.id,last.id , not first.id and not last.id

proc sort data =have ; by id stdate;run;

data want_st want_end want_m;

set have;

by id stdate;

if first.id then output want_st;

if not first.id and not last.id then output want_m;

if last.id then output want_end;

run;

I have the same problem with the id which has 3 records.

Thank you for your answers I will check the macros tomorrow.

Re: getting multiple rows of dates for a subject into a single row

Kc2 — Fri, 15 Jan 2016 02:21:05 GMT

Forgot to mention that I tried the tranpose method as well before posting my question on the forum but becasue of the different side effects variable per period of drug intake the tranpose is not working either.

Re: getting multiple rows of dates for a subject into a single row

FreelanceReinh — Fri, 15 Jan 2016 10:57:15 GMT

Transposing data using PROC SUMMARY's IDGROUP option as suggested by @Ksharp is a great idea. You can find background information about this technique and comparisons to more traditional methods in these two articles:

Re: getting multiple rows of dates for a subject into a single row

Kc2 — Fri, 15 Jan 2016 15:49:27 GMT

Thank you everyone. Ksharp's solution worked.