topic Re: Multiple selection using proc sql in SAS Programming

Multiple selection using proc sql

Eleenrose — Wed, 21 Aug 2013 08:49:32 GMT

Good morning Everybody,

I am totally new to sas programming, and I am trying to realise a huge data analysis with sas.

I have a database in access which is exported to sas and would like to do some data analsis.

the simple logic I am trying to realise is as follows.

1.I

need to calculate the total number of fax, call or email is made in the whole database.

2. calculate the same for last 6 months.

3. for each user

{

calculate the no: of fax, email, phone in each of the last 6 months.

}

so once the table is exported and in access environment it looks something like attached image

I currently realised the total values (overall value, total for month1 to month6, total email, fax, phone for month 1-6, total successful email/Fax/phone for month 1-6, total unsuccessfull email/fax/phone for month 1-6. which is around 110 fields. )

which i do not feel as the best solution to write each and every code(which is repeatative) in proc sql.

1. I am trying to find some optimal way how I can solve it.

2. I want to know how to put the whole name in a loop and calculate for each name the no: of ..fax/phone/email/success/unsucces etc.

I may write the logic this way

for each(name)

{

count no: of email month 1,

count no: email month2...month6

count no: of Fax month 1...month 6

count no: phone month1-month 2

save in the data analysis table

}

this is how I perform it currently

-----------------------------

Proc sql;

select

count (name)

into :count_fax_mon1

from total_6months

where

name<>""

and

communication = "Fax"

and

comm_date = "&mon1";

update communication_statistic

set Fax_mon1 = "&count_fax_mon1";

quit;

run;

---------------------------

any help is appreciated..

Thanks and regards,

Eleenrose

Re: Multiple selection using proc sql

jakarman — Wed, 21 Aug 2013 09:54:12 GMT

This small dataset (as it is coming from MS-Access) you want to do some reporting on.

You did not mention you environment/tools of SAS you are using. Also nothing on a support staff,

- Eguide is nice in click/for some results

- A BI/Di team could do the coding work for you.

Reporting in SAS is done for example with Base SAS(R) 9.4 Procedures Guide (Proc report)

No need to prepare counts as a dataset tabel in advance before presenting it. It is done all within that procedure.

Re: Multiple selection using proc sql

Patrick — Wed, 21 Aug 2013 10:00:27 GMT

You will need to provide us some data (eg. a subset of your sas table) in order to give you some code.

As you're new to SAS coding and I assume this is a one-off task I suggest you use SAS EG and the wizards there. That should make it quite a bit easier for you. You will need to skill-up a bit both with using SAS EG and SAS coding.

Re: Multiple selection using proc sql

Eleenrose — Wed, 21 Aug 2013 11:06:52 GMT

Thanks Jaap and Pratrick for the reply.

even though it is an access data, it is not as small and it contains around 4000 x 25 cells.

Therefore I find sas ideal and I need it to be updated only once a month. that means it suites with sas. I am interested in working with sas learning soemthing new..

I do not need a capsule code that I can use, but my question is, how can I have a loop in proc sql probably nested loop to get it done?

I found two possibilities 1. macro, 2 proc tabulate. I can also provide a sample table of input data.

Re: Multiple selection using proc sql

jakarman — Wed, 21 Aug 2013 11:30:45 GMT

Thx for the sizing....

4000 * 25 cells will be in the 10's of Mb's on disk (*.mdb - *.sas7bcat). Very tempting to use SASFILE option to get all data to internal memory of you computer. By that optimal performance while generating reports.
Being used to see dataset of 10's of Gb's and 4M instead of 4k records we should not bother about performance in your case.

The mentioned "proc report" is something similar as "proc tabulate" but report is tending to be used more last years.

Does not care. You should have access to Eguide. Using this is the most simple approach. The first hurdle is you can still code but there are lot of menu-s helping you the code being generated. There are options to define tables to build cells counts in the lay out you showed. Viewing the code is possible ... proc tabulate or report... being used.

Re: Multiple selection using proc sql

Eleenrose — Wed, 21 Aug 2013 11:56:35 GMT

Hi Jaap, Thanks for the suggestin. let me go through Eguide.

I had a quick look about sas Enterprise guide and came to know it is an application that needed to be installed in the system. But unfortunately I do not have access to it at my work place. so I really need to find a solution with proc sql, data sets or proc tabulate.

Re: Multiple selection using proc sql

ballardw — Wed, 21 Aug 2013 14:52:43 GMT

If the date is a SAS date type I think you can get much of this with the various reporting procedures directly and not a lot of data manipulation.

Something like this for all records;

proc tabulate data= yourdata;

class name;

class communication;

table communication, n; /* count of all communication types in data base*/

table name*communication, n; /* count of each communication type by name in database*/

run;

/* for within "last six months add where clause, good idea to have date in SAS date format*/

proc tabulate data= yourdata;

where comm_date >= '01MAR2013'd ; /* just an example date*/

class name;

class communication;

class comm_date;

format comm_date yymmd7.;

table communication, (comm_date all)*n ; /* count of communication by month and total since date given*/

table name*communication, (comm_date all)*n; /* count by name and communication by month and total*/

run;

My German isn't very good, but it looks like your example had some other status variable that might have been a success/failure. If that was coded in a 1/0 manner then it could be incorporated to give those percents shown in your example.

Re: Multiple selection using proc sql

Reeza — Wed, 21 Aug 2013 15:13:10 GMT

Look at Proc Freq. That will provide all the reports you're asking for easily enough, if you're not finicky for formatting. And your data is not considered "big" or even large . Access could also handle those reports in some basic queries.

Here's a good basic intro:

http://www.ats.ucla.edu/stat/sas/modules/descript.htm

The proc tabulate example above will work, but is more complex code for a beginner.

Proc freq data=yourdata;

table communication;

run;

If you need more help, I suggest posting what your tables look like at the least and sample data at best.

Re: Multiple selection using proc sql

Reeza — Wed, 21 Aug 2013 15:15:12 GMT

And loops in SAS are generally* used to go across columns not down rows, SAS processes one row at a time compared to SQL which may bring in the whole dataset into memory at once.

*There are exceptions to this, but for a beginner you wouldn't worry about it.

Re: Multiple selection using proc sql

Eleenrose — Wed, 21 Aug 2013 16:10:15 GMT

HI Balladw,

Thanks for the steps and this is how I wanted to have and it works with my project.

I can see the output in output window, but if I out it to a table, I cannot understand the result (out=test_tabulate) properly. But I tried to export it to excel table and I get similar to the one I had in output window.

But I need it in access db and unfortunately not in excel.

Anybody have any suggestions?

Thanks again for all sugestions and help..

Re: Multiple selection using proc sql

Reeza — Wed, 21 Aug 2013 16:37:10 GMT

Use proc freq and the output table from there and then have Access reports format it.

Re: Multiple selection using proc sql

LarryWorley — Wed, 21 Aug 2013 17:11:09 GMT

Following up on Reeza's suggestion, you can either write the table directly to the access database if you have Access to PC Files installed. Or you can write to an excel or csv file and have MS/Access read that file.

Re: Multiple selection using proc sql

jakarman — Wed, 21 Aug 2013 17:12:47 GMT

I a getting the feeling that something like an Olap or pivotable is being build.

"Poor man OLap" http://www.nesug.org/proceedings/nesug03/ad/ad008.pdf

The core is using proc summary/means (advice use the class ) to build a dataset with all aggregated information