08-21-2013 04:49 AM
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.
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
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
communication = "Fax"
comm_date = "&mon1";
set Fax_mon1 = "&count_fax_mon1";
any help is appreciated..
Thanks and regards,
08-21-2013 05:54 AM
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.
08-21-2013 06:00 AM
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.
08-21-2013 07:06 AM
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.
08-21-2013 07:30 AM
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.
08-21-2013 07:56 AM
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.
08-21-2013 10:52 AM
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;
table communication, n; /* count of all communication types in data base*/
table name*communication, n; /* count of each communication type by name in database*/
/* 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*/
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*/
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.
08-21-2013 12:10 PM
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..
08-21-2013 01:11 PM
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.
08-21-2013 11:13 AM
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:
The proc tabulate example above will work, but is more complex code for a beginner.
Proc freq data=yourdata;
If you need more help, I suggest posting what your tables look like at the least and sample data at best.
08-21-2013 11:15 AM
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.
08-21-2013 01:12 PM
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