topic Base query in ODS and Base Reporting

Base query

toffee — Sat, 27 Mar 2010 11:34:44 GMT

I have this data set.

Group Student Point
A0001 Thomas 100
A0001 Sue 90
A0001 Chan 81
A0002 Ray 66
A0002 Jacky 67
A0003 Aaron 69
.....
A1500 Gigi 78

In the above example, I would like to create data set for individual group...I could have 1500 groups.

Dataset A0001 should have student from A0001 only.

Can anyone help?

Re: Base query

Cynthia_sas — Sat, 27 Mar 2010 15:03:03 GMT

Hi:
Since you are posting this in the ODS and Base SAS Reporting forum -- there is an automated way to create output REPORTS (different from output DATASETS) using ODS and the NEWFILE= option. For example, if you ran a PROC PRINT using NEWFILE= BYGROUP:
[pre]
ods html file='bygrp1.html' newfile=bygroup style=sasweb;
proc print data=student;
by group;
run;
ods html close;
[/pre]

Then if you had 10 groups, the HTML reports for each group would be nameed BYGRP1.HTML thru BYGRP10.HTML. However, these would be REPORT files, not SAS datasets.

In order to create DATASETS, you will have to use some kind of repetitive logic, either in a DATA step program, PROC SQL or even a PROC SORT step (a DATA step approach and a PROC SORT approach are shown below):
[pre]
**** DATA step example;
data work.A0001 work.A0002 work.A0003;
set student;
if group = 'A0001' then output work.A0001;
else if group = 'A0002' then output work.A0002;
else if group = 'A0003' then output work.A0003;
run;

*** PROC SORT example;

proc sort data=student out=work.A0001;
by group student;
where group eq 'A0001';
run;

proc sort data=student out=work.A0002;
by group student;
where group eq 'A0002';
run;
[/pre]

To create a program where you did not need to know the names or number of GROUP values ahead of time, you would need to move into the world of SAS Macro processing.

These papers are a good introduction into the SAS Macro facility and should give you some ideas about how to automate the process of creating multiple SAS datasets from your one big file.
http://www2.sas.com/proceedings/sugi28/056-28.pdf
http://support.sas.com/resources/papers/proceedings09/151-2009.pdf
http://www2.sas.com/proceedings/sugi30/130-30.pdf
http://support.sas.com/resources/papers/proceedings09/200-2009.pdf

cynthia

Re: Base query

toffee — Sat, 27 Mar 2010 18:08:24 GMT

I am interested on this part, I was doing exactly as in the example, but what if i have hundreds of groups? Is that any better way?

**** DATA step example;
data work.A0001 work.A0002 work.A0003;
set student;
if group = 'A0001' then output work.A0001;
else if group = 'A0002' then output work.A0002;
else if group = 'A0003' then output work.A0003;
run;

Re: Base query

sbb — Sat, 27 Mar 2010 18:13:56 GMT

Yes, using SAS MACRO language, as Cynthia replied. You will end up with a general-purpose SAS DATA step that reads your SAS file (create an index for performance) and either use a WHERE statement or generate your list of unique group values and use the macro code to generate your DATA statement and your IF/THEN OUTPUT; code, in a similar form as you have demonstrated in your reply.

Scott Barry
SBBWorks, Inc.

Re: Base query

Cynthia_sas — Sat, 27 Mar 2010 18:27:50 GMT

Hi:
As I said in my previous post:

To create a program where you did not need to know the names or number of GROUP values ahead of time, you would need to move into the world of SAS Macro processing.

The links to papers at the end of the post should give you a start at understanding SAS Macro processing.

cynthia