BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Havi
Obsidian | Level 7

Hi All

Please please help. I have this really large dataset that I need to clean up so I need this process to be optimized the best way possible.

Please see below for sample records:

  

INPUT:
SEQNum1Num2ClassCostTimeID
11213.050A123
21213A040A123
31218A6010A123
OUTPUT:
SEQNum1Num2ClassCostTimeID
21213A090A123
31218A6010A123

I need to search through all the data to see where num1 and num2 match and where cost = 0 and summarize the time accordingly in 1 record along with the class as per the above. I require the desired output.

Thanks for the help.

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

just to enforce the sort...

proc sort in=have out=have;

by num1 num2 seq;

run;

data want;

set have;

by num1 num2;

if last num2;

run;

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20

If data is already sorted (as in your example), it could be done in one step.

What about Class and ID, to you want the value from the last row of the ones that you want to condense?

Try to elaborate using BY, FIRST./LAST. and RETAIN.

Data never sleeps
Havi
Obsidian | Level 7


Thanks for the response LinusH. Ok Ignore the question above. Let me rephrase.

Ideally I want to retain the last records class and ID where num1 and num2 are the same. My condition will be if num1 and num2 are the same and cost = 0 than take the last record.

eg:

INPUT:
SEQNum1Num2ClassCostTimeID
11213.050A123
21213A060A123
31218A6010A123
OUTPUT:
SEQNum1Num2ClassCostTimeID
21213A060A123
31218A6010A123

Please advise.

Thank you.

DBailey
Lapis Lazuli | Level 10

just to enforce the sort...

proc sort in=have out=have;

by num1 num2 seq;

run;

data want;

set have;

by num1 num2;

if last num2;

run;

Havi
Obsidian | Level 7

Thanks for your help.

Is this way the best way to select the last record for a dataset were num1 and num2 are the same?? Also cost will have to be 0.

My data set has 80612210 obs. I dont want this process to take too long as I have quite a few data steps after this one.

line2ellen
Calcite | Level 5

You need to have the data sorted in the order you want it, and exclude the data you don't want. If your data is already sorted, put the where clause on the set statement. To save time/space, only keep the variables you need. (I'm not sure if you need to keep seq after the data is sorted, for example.)

proc sort data=have(keep=num1 num2 seq class cost time ID where=(cost=0)) out=haveSorted(drop=cost seq);

  by num1 num2 seq;

run;

data want;

  set haveSorted;

  by num1 num2;

  if last.num2 then output;

run;

Haikuo
Onyx | Level 15

Not very sure if this is what you are after, but it seems to have the expected output for your as-is incoming data:

data have;

input SEQ Num1 Num2 Class:$1. Cost Time ID :$4.;

cards;

1 12 13 . 0 50 A123

2 12 13 A 0 60 A123

3 12 18 A 60 10 A123

;

/*if pre-sorted by num1 num2*/

data want;

  set have;

  by num1 num2;

  if first.num2*last.num2=1 then output;

else if last.num2 and cost=0 then output;

run;

Haikuo

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 1255 views
  • 4 likes
  • 5 in conversation