topic Re: Flagging records with specific values in SAS Programming

Flagging records with specific values

Ihsan-Mahdi — Mon, 16 Sep 2024 23:24:16 GMT

Hello,

I have a data set with multiple string variables, I would like to flag records with specific values AND exclude others. The values I want include S00-S10-S20-S30-S40-S50-S60-S70-S80-S90 only (example below). Is there a way to do that using arrays or something else? Thank you so much 🙂

Example of records wanted are in Red

Obs	Var1	Var2	Var3	Var4	Var5	Var6	Var7
2	S05	S05	S00	S01	S00
3	S20	S10
4	S29	S60	S60
6	S20	S61
7	S16	S60	S01	S01	S01	S01	S06
10	S01	S50	S50	S00
12	S36	S32	S40	S40
13	S70
14	S42	S42	S70	S80	S80	S80	S70
19	S42	S53	S40
20	S06	S00	S60	S70
21	S00	S40	S70
22	S42	S40	S70
23	S02	S02	S00	S40	S40	S40	S40
24	S32	S70	S01
25	S42	S62	S42	S60	S80	S50	S50

Re: Flagging records with specific values

Ksharp — Tue, 17 Sep 2024 00:58:29 GMT

data have;
infile cards truncover expandtabs;
input Obs	(Var1	Var2	Var3	Var4	Var5	Var6	Var7) ($);
cards;
2	S05	S05	S00	S01	S00	 	 
3	S20	S10	 	 	 	 	 
4	S29	S60	S60	 	 	 	 
6	S20	S61	 	 	 	 	 
7	S16	S60	S01	S01	S01	S01	S06
10	S01	S50	S50	S00	 	 	 
12	S36	S32	S40	S40	 	 	 
13	S70	 	 	 	 	 	 
14	S42	S42	S70	S80	S80	S80	S70
19	S42	S53	S40	 	 	 	 
20	S06	S00	S60	S70	 	 	 
21	S00	S40	S70	 	 	 	 
22	S42	S40	S70	 	 	 	 
23	S02	S02	S00	S40	S40	S40	S40
24	S32	S70	S01	 	 	 	 
25	S42	S62	S42	S60	S80	S50	S50
;

data want;
set have;
array x{10} $ _temporary_ ('S00' 'S10' 'S20' 'S30' 'S40' 'S50' 'S60' 'S70' 'S80' 'S90');
array v{*} $ Var:;
output=1;
do i=1 to dim(v);
 if not missing(v{i}) and v{i} not in x then do;output=0;leave;end;
end;
if output then output;
drop output i;
run;

Re: Flagging records with specific values

Ihsan-Mahdi — Tue, 17 Sep 2024 01:25:08 GMT

Thank you! Is there a way to make _temporary_ start with the 3 character value rather than equaling it? I selected only 3 characters in my example for simplicity, what I actually have in my data are values with variable numbers of characters. I want the ones that start with the 3 I mention in my example list.

Re: Flagging records with specific values

Ksharp — Tue, 17 Sep 2024 01:31:06 GMT

OK. You can change IN operator into IN: (with colon operator).

data want;
set have;
array x{10} $ _temporary_ ('S00' 'S10' 'S20' 'S30' 'S40' 'S50' 'S60' 'S70' 'S80' 'S90');
array v{*} $ Var:;
output=1;
do i=1 to dim(v);
 if not missing(v{i}) and v{i} not in: x then do;output=0;leave;end;
end;
if output then output;
drop output i;
run;

Re: Flagging records with specific values

Patrick — Tue, 17 Sep 2024 01:40:59 GMT

@Ksharp To make this work with my environment and SAS version I also needed to explicitly define the length of the array elements.

data want;
  set have;
  array x{10} $3 _temporary_ ('S00' 'S10' 'S20' 'S30' 'S40' 'S50' 'S60' 'S70' 'S80' 'S90');
  array v{*} $ Var:;
  out_flg=1;
  do i=1 to dim(v);
    if not missing(v{i}) and v{i} not in: x then
      do;
        out_flg=0;
        leave;
      end;
  end;

  if out_flg then output;
  drop out_flg i;
run;

Re: Flagging records with specific values

Ihsan-Mahdi — Tue, 17 Sep 2024 01:47:15 GMT

I'm sorry, it seems that I'm doing something wrong! Data want keeps having 0 observations!! To help clarify, the string variables in my set are named "ICD_10_dx_1 to ICD_10_dx_50".

This is the code I used based on yours:

data want;
set have;
array x[10] $ _temporary_ ('S00' 'S10' 'S20' 'S30' 'S40' 'S50' 'S60' 'S70' 'S80' 'S90');
array v[*] ICD_10_dx_1-ICD_10_dx_50;
output=1;
do i=1 to dim(v);

if not missing(v{i}) and v{i} not in: x then do;output=0;leave;end;
end;
if output then output;
drop output i;
run;

data want;
set have;
array x{10} $ _temporary_ ('S00' 'S10' 'S20' 'S30' 'S40' 'S50' 'S60' 'S70' 'S80' 'S90');
array v{*} $ ICD_10_dx_:;
output=1;
do i=1 to dim(v);
if not missing(v{i}) and v{i} not in: x then do;output=0;leave;end;
end;
if output then output;
drop output i;
run;

Where am I going wrong?!

Re: Flagging records with specific values

Patrick — Tue, 17 Sep 2024 01:51:13 GMT

Try if adding an explicit length for the temporary array elements will resolve this issue. That's what I had to do in my environment.

Re: Flagging records with specific values

Ihsan-Mahdi — Tue, 17 Sep 2024 01:51:39 GMT

It worked after specifying the length!! Thank you Again for your time and brilliance 🙂