topic Re: Unordered dataset in SAS Procedures

Unordered dataset

Mat_Zan — Sat, 14 May 2011 14:09:48 GMT

Hi, I've got an unordered dataset with 3 lists of datas, ordered by date. I have to obtain a dataset where, for every lines, my observation will be reported to the same date.
Can someone help me please?

n.b. proc sort is not enough.

Re: Unordered dataset

DBailey — Sat, 14 May 2011 14:42:59 GMT

I don't understand your requirements. An example of your data and the desired output would help.

Re: Unordered dataset

Cynthia_sas — Sat, 14 May 2011 14:59:31 GMT

Hi:
I am having a hard time visualizing what your input data looks like, what you mean by "lines" and what you mean by "lists".

For example, you say you have an unordered dataset with "3 lists of datas", ordered by date. To me the 3 "lists" could be 3 different, but related variable names, such as LIST1, LIST2 and LIST3:
[pre]
Date List1 List2 List3
01/01/2011 alan bob charlie
02/22/2011 alysa barb carla
03/23/2011 al beau carl
04/14/2011 andrea belle ceci
[/pre]

OR, the data could be structured completely differently:
[pre]
Date Name ListNum
01/01/2011 alan list1
01/01/2011 bob list2
01/01/2011 charlie list3
02/22/2011 alysa list1
02/22/2011 barb list2
02/22/2011 carla list3
03/23/2011 al list1
03/23/2011 beau list2
03/23/2011 carl list3
04/14/2011 andrea list1
04/14/2011 belle list2
04/14/2011 ceci list3
[/pre]

Then, you say that you need a dataset "where, for every lines, my observation will be reported to the same date. What I imagine is something like this EVALUATION data, added to the second data example:
[pre]
Date Name ListNum Evaluation
01/01/2011 alan list1 Performed all tasks on test
01/01/2011 bob list2 Did not finish test
01/01/2011 charlie list3 Left early will retake
02/22/2011 alysa list1 Was not prepared for test
02/22/2011 barb list2 Most accurate
02/22/2011 carla list3 Finished first
03/23/2011 al list1 Did not finish
03/23/2011 beau list2 Had letter to test verbally
03/23/2011 carl list3 Missed last page
04/14/2011 andrea list1 Did not put name on test
04/14/2011 belle list2 Performed all tasks on test
04/14/2011 ceci list3 Finished second
[/pre]

Without any idea of what your input data looks like (a small example is very helpful) or what you output dataset or report needs to look like (again, a small example is very helpful), it is almost impossible to make constructive suggestions.

You also say that PROC SORT is not enough, which means that you must have some code already that you've tried and you have results that you've looked at and you know that PROC SORT is not doing what you want. Sharing that code would tell everybody a lot -- it would show whether you were reading in 1 file or multiple files. It would show whether you were starting with existing SAS datasets or a single dataset.

If, for example, you have a "main" file with your data and you have a second file of information that needs to be added to the main file, then you might need an SQL join or a DATA step MERGE.Your main file might need to be restructured before it suits your needs. However, without a clear idea of what your input data looks like and what your output data looks like, it is only a guessing game.

This is a very handy forum posting that explains how to post questions and still maintain the indenting of code and of input and output samples that you want to show:
http://support.sas.com/forums/thread.jspa?messageID=27609毙

cynthia

Re: Unordered dataset

Mat_Zan — Sat, 14 May 2011 15:04:25 GMT

here an exemple
1/1/2000----23
2/1/2000----24
3/1/2000---25

2/1/2000---30
3/1/2000---31
5/1/2000---32

1/1/2000---34
2/1/2000---25
5/1/2000----45

in my excel file this datas is "in line" not on column.
each list is "locally" ordered, I need to have a global order of my dataset, every line must have the same date.

Re: Unordered dataset

DBailey — Sat, 14 May 2011 20:50:18 GMT

Is it your wish to have the output look like this:
1/1/2000 23 34
2/1/2000 24 30 25
3/1/2000 25 31
5/1/2000 43 45

Re: Unordered dataset

Mat_Zan — Sat, 14 May 2011 22:54:56 GMT

> Is it your wish to have the output look like this:
> 1/1/2000 23 34
> 2/1/2000 24 30 25
> 3/1/2000 25 31
> 5/1/2000 43 45

Exactly, I need to have ordered observation because of the three sets of dates are unordered.

Re: Unordered dataset

Cynthia_sas — Sun, 15 May 2011 13:47:24 GMT

Hi,
Thanks for the data example.

My recommendation would be:
--read the data from Excel into a SAS dataset
--sort the data by date value
--use PROC TRANSPOSE to transpose the data so that the values for each date are all on one observation (there are options to control the naming of the final variables -- otherwise, you can just start with the default names).

This is a good paper on TRANSPOSE:
http://www2.sas.com/proceedings/forum2007/234-2007.pdf

and there are previous forum postings on using TRANSPOSE. And, here's the documentation:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#transpose-overview.htm

cynthia

Re: Unordered dataset

Mat_Zan — Sun, 15 May 2011 22:40:35 GMT

Thank you very much for the help!