topic How to group my data without using proc sql statement in SAS Programming

How to group my data without using proc sql statement

Kayla_Tan222 — Mon, 08 Jul 2019 01:29:55 GMT

Hi all, facing problem on how to group my data without using proc sql statement.

Because if I'm using proc sql statement , the answer will show in 'results' instead of 'output data'.

I want my answer in 'output data' because I want it to be saved in library in my computer.

Here's my code below if I'm using the proc sql statement.

libname pl201812 "\\kaiwksgh415thw5\Data\POLA\Claim\2018\201812\DBF";

Data pl201812.POLA_Fire_clm_incurred_201812;

Set pl201812.pola_clm_agg_201812;

WHERE lossyr_fy>=2012 and accyr_fy*100+accmth_fy<=201901 and zmajrsk="FIR";

run;

proc sql;

create table pola as

select lossyr_fy,claim,rskno,zsumsi,risktype,sum(zclmpd) as zclmpd,sum(zclmos) as zclmos

from pl201812.POLA_Fire_clm_incurred_201812

group by 1,2,3,4,5;

run;

Re: How to group my data without using proc sql statement

heffo — Mon, 08 Jul 2019 03:39:06 GMT

If you don't want the print out of the SQL you can add the noprint setting. Your code, as it is, still creates the data set in the work library. That is the line "create table pola as". SAS see that you don't have a library specified for your data set and it will assume that pola should be WORK.pola. Of course, if you want to save it between sessions you have to use "create table pl201812.pola as".

proc sql noprint;
    create table pl201812.pola as
    select lossyr_fy,claim,rskno,zsumsi,risktype,sum(zclmpd) as zclmpd,sum(zclmos) as zclmos
    from pl201812.POLA_Fire_clm_incurred_201812
    group by 1,2,3,4,5;
run;

Re: How to group my data without using proc sql statement

Tom — Mon, 08 Jul 2019 04:17:48 GMT

Because if I'm using proc sql statement , the answer will show in 'results' instead of 'output data'.

I seriously doubt that is true.

You didn't say what interface you are using to run SAS (Enterprise Guide, SAS/Studio, SAS Display Manager, something else) but none of them should be able to tell whether the dataset POLA was made with a data step:

data pola;
....

or with an SQL statement.

create table pola ....;