Comparing SAS strings and queries? or another solution. I'm stumped
I have a situation where, for each unique observation of a casenum, I would like to run different queries and arithmetic operations between different observations of the "code" for that "casenum" (see below). For example, for casenum 1234567 I would like to subtract the data for code 0200 - code 0234 or 531 - 53. Keep in mind that there are thousands of cases in this dataset. Is there an easy way to do this or to perform string comparison with a specific one.
Note that casenum and code are character variables and data is numeric
Here's an example of how a dataset is structured:
casenum code data 1234567 0123 4597 1234567 0234 53 1234567 0100 789 1234567 0200 531 1234567 0300 354 1111112 0123 79 1111112 0234 78 1 111 112 0100 77 1111112 0200 7954 1111112 0300 35
Here is the logic, although it is probably syntactically incorrect in what I am trying to do.
For code observations, where the casenum is the same, within these casenums
I would like it to determine if data for code 0234 + data for code 0100 is data for code 0123 ne data for code 0200, then newvariable = 'YES'
In other words, I would like this to check if 53 + 789 - 4597 ne 531. After this and other similar kinds of tests running inside casenum 1234567, I would like it to move to the next casenum and run these same tests for this casenum.
Keep in mind that this dataset contains hundreds of thousands of observations.
I don't understand what is your logic for the subtraction part of the code, but for the selection of a group of lines that I can suggest. At first glance, I would get a list of different values for casenum.
proc sql;
select distinct casenum
into :casenum_list separated by ' '
from dataset;
quit;
Now that you have a list of all the different casenum values, I will iterate over the lines, following whatever logic you need.
Maybe another proc sql is being used, for example:
%MACRO DOIT;
%LET COUNT=1;
%DO %UNTIL (%SCAN(&casenum_list,&COUNT) EQ);
%LET CASENUM_VAR=%SCAN(&casenum_list,&COUNT);
PROC SQL;
SELECT
<INSERT SOME SQL LOGIC HERE>
FROM
DATASET
WHERE CASENUM=&CASENUM_VAR;
QUIT;
%LET COUNT=%EVAL(&COUNT+1);
%MEND DOIT;
%DOIT;
Hope this helps. If you can get a better understanding of what you are trying to accomplish in the ranks, I can be more specific.
a source to share
If the formula is fixed (as it seems, as your example seems), then there shouldn't be any reason why you can't do a simple transposition and then explicitly declare the test.
/* Transpose the data by casenum */
proc transpose data=so846572 out=transpose_ds;
id code;
var data;
by casenum;
run;
/* Now just explicitly write your conditional expression */
data StackOverflow;
set transpose_ds;
if _0234 + _0100 - _0123 <> _0200 then newvariable="yes";
run;
Where so846572 = Your original dataset, transpose_ds = Transposed version, StackOverflow = final output.
Let us know if this expression should be dynamic for any reason. It should scale easily to the amount of data you mentioned without any problem. You could do the same with a hash, in a single pass of data.
a source to share
I don't think I actually have the information from your question to help, but I'll just throw it away ... If you want to do a string comparison, you can also use a data step. Assuming your data is sorted by casenum, you can use in the first place. And last. to determine when you have a new case and when you are in the last row of the treasury. Whether you want to sum data values between rows or make decisions based on the previous row for kazen listed multiple times.
Data work.temp ;
retain casenum_data ;
set lib.data ;
by casenum ;
if first.casenum then do ;
/* <reset hold vars> */
casenum_data = 0 ;
end ;
if code = "0200" or code = "234" then .....
if last.casenum then do ;
/* output casenum summary */
output ;
end ;
run ;
Post more information about the need and additional help.
a source to share