turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Proc Transpose problem

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-13-2012 06:25 PM

My code: (runs just fine and gives me a total of 198872 rows

proc sql;

create table t.test as

(select distinct *

from t.org a

left join t.status b

on

a.custid = b.custid

and

b.custid is not null);

order by custid;

quit;

I have several order dates that come over. The order dates are processed by custid order dates and can be multiple dates. Then we have multiple ship dates. I need to transpose the ones for the last 6 months. The table created above I have no more than 3 order dates per custid and 3 ship dates per custid. I need to spread all these across the board so there is only 1 row and do this in SAS. That is the way the boss wants it and cannot change the specs laid out before me. My first transpose and second are below.

proc transpose data = t.test out = t.test1 (drop=_name_ _label_) prefix = orderdt;

by custid orderdt;

var orderdt;

run;

this runs just fine.

proc transpose data = t.test out = t.test2 (drop=_name_ _label_) prefix = shipdt;

by custid shipdt;

var shipdt;

run;

This errors and says Data set t.test is not sorted in ascending sequence. The current by group has shipdt = 14dec2011 and the next by group has shipdt = 14oct2011. The Sas Syste stopped processing this step because of errors. There were 12350 observations read from the data set t.test. The data set t.test2 may be incomplete. When this step was stopped there were 0 observations and 0 variables.

When i go to the table nothing is in there. So it is seeing 12350 observations but due to the ascending sequence it errors out and does not continue processing. I have gone back and told it to ascend the shipdt but get same error. It is supposed to assume ascending order. I even tried foolling it by doing a descending and that did not work. I tried putting in the proc transpose descending and that did not work. I tried taking the original code from above and doing an asc on the shipdt and put order by custid shipdt asc; quit; and then placing it between my first transpose and 2nd and still got the same error. I even tried not doing a proc transpose on the orderdt and only working with shipdt and still get the same error. I am not sure what to try next. Any help???

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-13-2012 06:38 PM

I'm surprised that your first proc transpose ran, as your use of proc sql only included:

order by custid;

rather than

order by custid orderdt;

Regardless, before your 2nd proc transpose, run:

proc sort data= t.test;

by custid shipdt;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-13-2012 06:40 PM

I actually had that in the original but just neglected to post it. It is hard to put in my code as it is in a work computer and I am typing this in the home computer. Not allowed to surf to find answers to problems on work system. So have to transpose from another computer screen. Anyway, I will try this proc sort and see if the error disappears. Will let you know!!!!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

02-13-2012 06:45 PM

Yeah. It worked. Thank you so much!!!!!