|
|
|
Frequently Asked Questions about SAS Software
The following Question & Answer List is based on SAS questions received by the author.
Record Selection
| Question | Answer |
|---|---|
I have this horrible IF statement:
.....
IF SUBSTR(accno,4,6)
IN('123456','789101','234567',
'891011','121314','567890',
'345678','456789')
THEN DO;
INPUT amount 41-51 .2;
.....
Is this the most efficient way of doing this ? |
How about using a SAS format? They are pretty quick and you will be making any changes at the top of the code, instead of in the middle. Also the format can be reused without having to type out the list again.
eg.: PROC FORMAT;
VALUE $acctest
'123456','789101','234567',
'891011','121314','567890',
'345678','456789' = 'Y'
OTHER = 'N';
RUN;
.....
IF PUT(SUBSTR(accno,4,6),$acctest.) = 'Y'
THEN DO;
INPUT amount 41-51 .2;
.....
|
| I have 2 tables containing similar records, but the matching I want to carry out would result in a many-to-many match, whereas what I want is a randomised 1-to-1 match with no duplicates.
How could this be achieved ? |
The following code should do the job. In this example the SASHELP.CLASS table is first split into males and females, then they are randomly matched into partners, where possible, by age:
* generate sample data for demo,
with key and random number *;
DATA males (KEEP = name age __random)
females (KEEP = name age __random)
;
SET sashelp.class;
__random = RANUNI(0);
IF sex = 'M' THEN OUTPUT males;
ELSE OUTPUT females;
RUN;
* sort 1st table by key and random number *;
PROC SORT DATA = males;
BY age __random;
RUN;
* sort 2nd table by key and random number *;
PROC SORT DATA = females;
BY age __random;
RUN;
* generate group index within each key group *;
DATA males (KEEP = name age __index);
SET males;
BY age;
RETAIN __index 0;
IF first.age THEN __index = 0;
__index + 1;
RUN;
* generate group index within each key group *;
DATA females (KEEP = name age __index);
SET females;
BY age;
RETAIN __index 0;
IF first.age THEN __index = 0;
__index + 1;
RUN;
* merge records from 2 tables using matching keys
and matching group index *;
DATA partners (KEEP = name1 name2 age);
MERGE males (RENAME = (name = name1))
females (RENAME = (name = name2))
;
BY age __index;
RUN;
|
