topic Re: how do i in proc sql ? in SAS Studio

using proc sql make a dataset ,Pick maximum AON for each year and join with main data

Shrutibhatnagar — Sat, 16 Mar 2019 12:55:44 GMT

Hello

i have smb1 dataset which has 3 different AGE OF NETWORKS for 3 different years

i want to select only the MAXIMUM AON using proc sql and also use this maximum AGE OF NETWORKS against the Product id

as 3 different AGES are not possible . how to create the data set for age of network along one id .

Re: how do i in proc sql ?

PaigeMiller — Sat, 16 Mar 2019 11:30:51 GMT

Please show us a representative portion of the data that you are working on. Please show us the output you desire from the representative portion of your data.

Please go back and edit the title to something a little more descriptive of the problem.

Re: how do i in proc sql ?

Shrutibhatnagar — Sat, 16 Mar 2019 13:01:37 GMT

hello same has been edited !!

Re: how do i in proc sql ?

Shrutibhatnagar — Sat, 16 Mar 2019 15:51:12 GMT

proc sql;
select distinct Max(AON ) AS Maximum_of_AON,smb.product_id
from class1.smb as Actual_AON
group by product_id
having AON=max(AON);
quit;

which gives :

Maximum_of_AON PRODUCT_ID

1281

114832053

which is not the required result i want maximum of AON for all product id's

Re: how do i in proc sql ?

Kurt_Bremser — Sat, 16 Mar 2019 17:29:43 GMT

You added a spreadsheet, not a dataset. The proper method for posting SAS datasets is data steps with datalines.

A re-import of a spreadsheet is close to 100% sure to be different from the original dataset.

Re: how do i in proc sql ?

PGStats — Sat, 16 Mar 2019 19:59:12 GMT

You seem to need:

proc sql;
select 
    AON AS Maximum_of_AON,
    product_id
from class1.smb
group by product_id
having AON=max(AON);
quit;