topic PROC SQL to SAS DATA STEP in SAS Enterprise Guide

PROC SQL to SAS DATA STEP

woo — Wed, 21 Oct 2015 21:34:29 GMT

hello friends - if you can tell me how can i write below proc sql query in data step? thank you

PROC SQL;
CREATE TABLE WORK.Query2_for_TEST3 AS SELECT DISTINCT TEST3.date,
TEST3.Item_number,
(COUNT(TEST3.found)) AS COUNT_OF_found
FROM WORK.TEST3 AS TEST3
GROUP BY TEST3.Item_number
ORDER BY TEST3.Item_number;
QUIT;

Re: PROC SQL to SAS DATA STEP

ballardw — Wed, 21 Oct 2015 21:44:47 GMT

It would better to show start data and desired end.

DATA step probably wouldn't be the best as it has not equivalent of GROUP BY or DISTINCT.

I would try if found is numeric:

proc summary data = work.test3 nway;

class Item_number date;

var found;

output out=qork.query2_for_test3 (drop= _type_ _freq_) n= count_of_found;

run;

Or did you need group by Item_number, date for your original query?

Re: PROC SQL to SAS DATA STEP

woo — Wed, 21 Oct 2015 22:13:11 GMT

so have this: date (in date format) (item_number and found both are numeric)

date item_number found
01oct14 123456 1

02oct14 123456 2

01oct14 789101 1

02oct14 789101 2

03oct14 789101 3

04oct14 789101 4

01oct14 111111 1

01oct14 222222 1

02oct14 222222 2

03oct14 222222 3

and want this

date item_number count_of_found
01oct14 123456 2

02oct14 123456 2

01oct14 789101 4

02oct14 789101 4

03oct14 789101 4

04oct14 789101 4

01oct14 111111 1

01oct14 222222 3

02oct14 222222 3

03oct14 222222 3

Re: PROC SQL to SAS DATA STEP

Astounding — Thu, 22 Oct 2015 00:46:01 GMT

A DATA step might look like this:

data want;

count_of_found=0;

do until (last.item_number);

set have;

by item_number notsorted;

count_of_found + 1;

end;

do until (last.item_number);

set have;

by item_number notsorted;

output;

end;

run;

Optionally, you could drop FOUND from the final data set.

Good luck.

Re: PROC SQL to SAS DATA STEP

LinusH — Fri, 23 Oct 2015 07:15:41 GMT

Why?