Hi SAS Community,
I have run the below proc freq.
proc freq data=QP.have;
tables Days_Past * arrears_band/missing nocol norow nopct ;
where product IN ('OD','Loan','Mort');
run;
This is the output.
Table of Days_Past by Arrears_Band
| ||||||||
| Arrears_Band | Total | ||||||
1 - 30 | 30 - 60 | 60 - 90 | 90 + | Current | NPNA | |||
Days_Past |
| 0 | 0 | 0 | 0 | 48 | 69 |
|
ÿ | Frequency | |||||||
0 | Frequency | 0 | 0 | 0 | 0 | 797 | 5 |
|
1 | Frequency | 300 | 0 | 0 | 0 | 0 | 613 |
|
10 | Frequency | 504 | 0 | 0 | 0 | 0 | 25 |
|
100 | Frequency | 0 | 0 | 0 | 1 | 0 | 107 |
|
101 | Frequency | 0 | 0 | 0 | 0 | 0 | 61 |
|
102 | Frequency | 0 | 0 | 0 | 0 | 0 | 103 |
|
103 | Frequency | 0 | 0 | 0 | 6 | 0 | 78 |
|
104 | Frequency | 0 | 0 | 0 | 1 | 0 | 37 |
|
105 | Frequency | 0 | 0 | 0 | 0 | 0 | 82 |
|
106 | Frequency | 0 | 0 | 0 | 1 | 0 | 78 |
|
107 | Frequency | 0 | 0 | 0 | 1 | 0 | 141 |
|
108 | Frequency | 0 | 0 | 0 | 1 | 0 | 103 |
|
109 | Frequency | 0 | 0 | 0 | 1 | 0 | 36 |
|
11 | Frequency | 165 | 0 | 0 | 0 | 0 | 30 |
|
Question:
In the above Table:
i) I need to get the values of the “Days_Past” column in ascending order
ii) Need to replace notation ÿ with the word missing or ‘ ’
iii) Need to get rid of the repeating word "Freequency"
I have tried with DATA, FORMATTED, and INTERNAL options in place of yellow-higlighed “freq” below but non works.
proc freq data=QP.have order=freq;
tables Days_Past * arrears_band/missing nocol norow nopct ;
where product IN ('OD','Loan','Mort');
run;
/*This excellent example provided by Ksharp also cannot be applied because I have about 700 values for the variable “Days_Past” */
data x;
input a & $10. b & $10.;
cards;
. .
Current Current
1 - 30 Delinquent
30 - 60 Default
60 - 90 NPNA
90+ Current
NPNA Current
;
run;
proc format ;
value $ fmt
' '=' Missing'
'Current' = ' Current'
'1 - 30' = ' 1 - 30'
'30 - 60' = ' 30 - 60'
'60 - 90' =' 60 - 90'
'90+' =' 90+'
'Default'= 'Default'
'Delinquent'= ' Delinquent'
'NPNA' = 'NPNA'
;
run;
proc freq order=formatted;
tables b*a/missing ;
format a b $fmt.;
run;
Could anyone help me on this?
Thanks
Mirisage
Wouldn't something like the following give you what you want?:
data need;
set have (rename=(Days_Past=_Days_Past));
Days_Past=input(_Days_Past, ?? 12.);
run;
proc freq data=need order=internal;
tables Days_Past * arrears_band/missing nocol norow nopct ;
where product IN ('OD','Loan','Mort');
run;
Not to be a PITA but haven't you asked multiple questions like this before?
Basically the BEST way to do it, is not to code to character values like you have but use numeric codes.
Ie rather than
.=' Missing'
0 = 'Current'
1= '1 - 30'
2= '30 - 60'
3='60 - 90'
4 ='90+'
5= 'Default'
6= ' Delinquent'
7= 'NPNA'
Then apply the format using a proc tabulate where you can control all the labels in the table.
With this method you don't need extra spaces in the labels which can mess up your reports and the sorts will work fine.
Hi Reeza,
Thanks.
This question is actually different than the several questions I have asked previously.
I wanted to attach my data set into this to make my question clearer.
However, I cannot attach it into this.
So, I have to do new posting and please have a look on it if you have time.
Thanks
M
Days_past is sorting in character order. The variable is probabably not numeric and is sorting properly if that is the case.
You may have modified the template that Proc Freq is using to do crosstabs, possibly from running an example somewhere as the label FREQUENCY normally will not appear in the output.
Try adding:
ods path sashelp.tmplmst(read) sasuser.templat(update);
before the proc freq to reset the search order of templates to use the default templates and see if that will fix the "Frequency" text problem.
Wouldn't something like the following give you what you want?:
data need;
set have (rename=(Days_Past=_Days_Past));
Days_Past=input(_Days_Past, ?? 12.);
run;
proc freq data=need order=internal;
tables Days_Past * arrears_band/missing nocol norow nopct ;
where product IN ('OD','Loan','Mort');
run;
Hi ballardw and Art,
Many thanks.
Hi Art,
What you have suggested works. Thanks.
I would like to ask your help to learn another aspect from your code.
data need;
set have (rename=(Days_Past=_Days_Past));
Days_Past=input(_Days_Past, ?? 12.);
run;
Above code performs sequentially what I have shown below:
1). It takes the character variable named Days_Past from work.have and renames it to _Days_Past
2). Then the _Days_Past variable is converted into a numeric variable and gives the name Days_Past
double question mark (??) modifier suppresses the printing of both the error messages and the input lines when invalid data values are read.
Am I correct?
Question:
How come then the following code does the job differently (I have explained the sequence after the below code).
data work.want (rename = (N_VAR1= VAR1
N_VAR2 = VAR2
N_VAR3 = VAR3));
set work.have ;
N_VAR1 = input(VAR1, 8.);
N_VAR2 = input (VAR2,8.);
N_VAR3 = input (VAR3, 8.);
drop VAR1
VAR2
VAR3
;
run;
1). First, it takes 3 character variables named VAR1, VAR2 and VAR3 from work.have dataset and then converts each one of them into numeric variables giving names N_VAR1, N_VAR2 and N_VAR3 respectively.
Whereas in your code, renaming was done first. How in the second code variable conversion happend first?
2). Then drops 3 character variables named VAR1, VAR2 and VAR3
3). Then it gets 3 numeric variables named N_VAR1, N_VAR2 and N_VAR3, and then renames them back into VAR1, VAR2 and VAR3
Is this correct?
Could you please help me to understand this differential behavior of seemingly same two codes.
Thanks
M.
The two sets of code appear to be doing mostly the same thing and, yes, your explanations appear to be correct. Are you obtaining different results from the two methods?
The principal differences I see between the two sets of code are that:
1. My code will silently set alpha characters to missing during the conversion
2. You drop the original variables while my code didn't (but easily could have)
As for whether one renames or drops in the set statement, the data statement or in the code itself, is probably just a matter of one's personal preference. I happen to like doing those things in the data and set statements as, there, I know the order in which the statements will be executed, namely alphabetically. Drops are done before Keeps which are done before Renames. Honestly, I've never even considered if there is such a precedence inherent when they are included as separate statements in the datastep.
Hi Art,
Thank you very much for this knowledge, i.e. "Alphabetical execution of "Drop", "Keep" "Rename" statements".
Regards
Mirisage
Mirisage,
Your last example doesn't work because you moved the RENAME from the SET to the DATA statement. It belongs on the SET statement (not as a matter of syntax, but as a matter of logic for this particular application).
Good luck.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.