BookmarkSubscribeRSS Feed
AZIQ1
Quartz | Level 8

Hi,

I want to run a report that gives me all the categories for all columns with percentages in one go. Like a single report. My tables could have >100 categorical (character) value fields. Issue is that it is taking very long and does not have enough memory to run for all fields - any ideas how to handle it

Have

IdGenderStateGrade
1MaleIN8
2FemaleMO8
3FemaleTX 
4NullTX 
5Null 6
6NullTX7

 

Want a single output 

Gendern%
M117%
F233%
Null350%
   
State  
IN117%
MO117%
TX350%
Blank117%
   
Grade  
6117%
7117%
8233%
Blank233%
5 REPLIES 5
rudfaden
Pyrite | Level 9

Can you show your current solution?

AhmedAl_Attar
Rhodochrosite | Level 12

Hi @AZIQ1 

If you have access to SAS Enterprise Guide or SAS Studio, you can use the "Characterize Data" task to analyze your table and produce similar results to your desired output.

 

Hope this helps

Ksharp
Super User
ods select none;
ods noresults;
ods output OneWayFreqs=OneWayFreqs;
proc freq data=sashelp.class ;
table _all_/ list;
run;
ods select all;

data temp;
set OneWayFreqs(drop=Table );
value=coalescec(of _character_);
keep value;
run;
data want;
 merge OneWayFreqs(keep=Table Frequency Percent) temp;
run;
rudfaden
Pyrite | Level 9
Maybe look into using proc report or tabulate instead. If that does not work, I would loop over the variable.
ballardw
Super User

Suggest: Provide a bit better description of your data. You can do that with Proc Freq and NLEVELS to report on the number of levels of each variable. Example:

ods select nlevels;
proc freq data=sashelp.class nlevels;
run;

The ODS select means that only the Nlevels table is generated which should not run out of memory but will give us an idea just how much stuff you are dealing with.

"Blank" is not a SAS value you will see. Depending on which procedure/approach you are dealing with you can use the MISSING option to get a count.

 

You can run into memory issues when generated large tables because the table with all the border information and such are stored in memory before writing out the results. If you have the default HTML destination and are sending the data to another ods destination that can seriously increase the memory needed. So you might have better results by closing the HTML destination and writing to only one destination like ODS RTF of PDF.

 

One basic syntax would look like:

proc tabulate data=yourSetNameHere;
   class _character_/missing;
   table _character_, n pctn;
run;

but missing values will be at the top of each as the default order for character values is alphabetic and blanks come before any other printable character.

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 371 views
  • 0 likes
  • 5 in conversation