topic Re: Group cell and count frequency in SAS Programming

Group cell and count frequency

bondtk — Mon, 10 Apr 2017 10:58:05 GMT

Hi,

I am attaching this excel file here, I have done some analysis in the analysis sheet to countif the data under certain groups,is it possible

to achieve that with SAS EG, please advise.

Re: Group cell and count frequency

RW9 — Mon, 10 Apr 2017 11:06:31 GMT

Well, firstly, you haven't attached any file.

Secondly, it wouldn't matter anyways as most people wont download files from the internet.

Thirdly, follow the guidance for posting questions - Post example test data in the form of a datastep following this post if you need help:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Finally, on your question, yes its perfectly possible to count data if it fits criteria. Without knowing the data its hard to give any specific help other then filtering datasets using where clauses and proc freq/means would jump to mind. I.e.

proc means data=have;

where abc="condition";

output out=want n=n;

run;

Re: Group cell and count frequency

Kurt_Bremser — Mon, 10 Apr 2017 13:15:53 GMT

First you need to make a SAS dataset out of your Excel spreadsheet. Once that has happened, use the macro suggested by @RW9 to convert your dataset into a data astep that can be posted here for reference.

If you have trouble importing the Excel data, we need to solve that first. Post the log from the import step, using the {i} button found on top of the posting window.

With your example data (data step, see above), supply an expected result.

Re: Group cell and count frequency

bondtk — Fri, 14 Apr 2017 04:02:43 GMT

I have got the dataset from excel file, not sure about the macro as I am very new to SAS , so I am not familiar with SAS macros.

what I am trying to achieve from this set is to get the count based on Amount_Financed falls in to categories from

0-75k, 75-150k, 150-300k, 300-500k.

Also how many falls between System Decision : AST, DEC and REF and how many are the consumer loans and how many are

non conusmer.

I have achieved that with excel formulas and pivot table , but need to achieve the same result with SAS.

I tried to use this SAS code:

data tk.feb_pivot;

set tk.volume_af_data_feb_pivot;

if Amount_Financed <=75000 then Lend_Level = 'Under75k';

else if 75000 GT Amount_Financed LE 150000 then Lend_Level = '75-150k';

else if 150000 GT Amount_Financed LE 300000 then Lend_Level = '150-300k';

else Lend_Level = '300k-500k'; run;

proc freq data=tk.feb_pivot;

tables Lend_Level/missing nocum ;

run;

Re: Group cell and count frequency

bondtk — Fri, 14 Apr 2017 04:07:28 GMT

Attaching the pdf shot of excecl file

Re: Group cell and count frequency

Kurt_Bremser — Fri, 14 Apr 2017 07:49:26 GMT

DO NOT post a .sas7bdat. Instead use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert your dataset to a data step, and post that here (use the "little running man" icon to preserve formatting). .sas7bdat files are often version/location/installation specific and may be unusable for others. A data step is just text.

Re: Group cell and count frequency

bondtk — Fri, 14 Apr 2017 09:22:03 GMT

Could yoiu please advise how to run this data step marco

I downloaded the file , extract the data and put it myfolders as datatodatstep.sas file

as I edit the autoexec

as per step 4 and run the code

%include '/folders/myfolders/data2datastep.sas' /lrecl=1000;

it gives an error:

%include '/folders/myfolders/data2datastep.sas' /lrecl=1000;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

63 %include '/myfolders/data2datastep.sas' /lrecl=1000;

ERROR: The path /myfolders/data2datastep.sas is not in the list of accessible paths when SAS is in the lockdown state.

ERROR: The %INCLUDE statement will not be executed due to a LOCKDOWN violation.

65 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

please advise what is this error, no idea about this.

Re: Group cell and count frequency

bondtk — Fri, 14 Apr 2017 10:42:38 GMT

RUN THIS CODE AS PER INSTRUCTIONS TO SET UP DATA2DATESTEP:

%data2datastep(cars,sashelp,,5)

NO ERROR , BUT NO RESULTS

NOTES 9: NOTE: The quoted string currently being processed has become more than 262 bytes long. You might have unbalanced quotation marks.

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space

between a quoted string and the succeeding identifier is recommended.

THIS is so frustrating to send this data to this forum....

Re: Group cell and count frequency

RW9 — Mon, 17 Apr 2017 14:24:19 GMT

Hi,

All we ask is that you take a minute or two to create some example test data in the form of a datastep. Most companies will not all users to download random files from the internet, hence that is not a viable option. We ask for the test data in the form of a datastep so that we can see both structure and content - this is important when handling dates/times, formatted values etc. It is very simple, say I need to show data with variablea as date and variableb as numeric, with id as char:

data have;
  informat variablea date9.;
  input id $ variablea variableb;
datalines;
ABC 01JAN2014 12
DEF 04FEB2014 36
;
run;

You will notice that the code appears in a grey box, this is above the post area, either the {i} or the SAS run symbol to add a code block. Remember, we cannot see what you are working on, for us to help we need to be able to see it.

Re: Group cell and count frequency

Tom — Mon, 17 Apr 2017 14:49:39 GMT

So if I just copy and paste the counts from your PDF I can make some sample data.

data have ;
  input Amount @;
  do system_decision='DEC','AST','REF' ;
    input count @;
    output;
  end;
cards;
10000  843 2987 3783 7613
200000   4    0   26   30
400000   3    0    4    7
100000  27   53  281  361
;

I just used a number in the middle of the range to have an actual AMOUNT instead of a range.

You can then define a FORMAT to group your data for your.

proc format ;
  value amount 
  0-75000 = '0-75k'
  75000-150000 = '75k-150k'
  150000-300000 = '150k-300k'
  300000-500000 = '300k-500k'
  other = 'Unknown'
  ;
run;

Then to get the cross tab just use PROC FREQ. You can play with the options if you only want to see the counts and not the percentages. For your real data you will not need the WEIGHT statement.

proc freq data=have ;
 tables Amount * System_decision ;
 format amount amount. ;
 weight count;
run;