Help using Base SAS procedures

Proc Transpose problem

Reply
Regular Contributor
Regular Contributor
Posts: 238

Proc Transpose problem

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???

PROC Star
Posts: 7,363

Proc Transpose problem

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;


Regular Contributor
Regular Contributor
Posts: 238

Proc Transpose problem

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!!!!

Regular Contributor
Regular Contributor
Posts: 238

Proc Transpose problem

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

Ask a Question
Discussion stats
  • 3 replies
  • 1459 views
  • 0 likes
  • 2 in conversation