BookmarkSubscribeRSS Feed
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

3 REPLIES 3
art297
Opal | Level 21

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;


tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 5014 views
  • 0 likes
  • 2 in conversation