keeping certain values

[sks521]

Hi folks,

How do I write syntax for following condition please;

I have variables diag_01 to diag_06 and teat_01 to treat_12. I want to keep observations if;

any of diag_01 to diag_06 have any of these values;

'251', 'J441','J450', 'J45', 'J451','J458', 'J459', 'J46', 'J46X', 'R062'

Or

WHERE any of treat_01 to treat_12 have;

'25'

BUT 

diag_01 to diag_06 not in;

'J21', 'J210', 'J218', 'J219', 'J05', 'J050'

Thanks

[andreas_lds]

You need to define two arrays: one for the diag-variables and the other one for the treat-variables. Then use loops to check the variables in the arrays and set a flag variable. After the loops use subsetting-if or a similar statement to write the observation to the dataset (or prevent writing the observation).

If you want tested code, you have to provide data in usable form: a data step using datalines or cards.

EDIT: This seems to be a follow-up to https://communities.sas.com/t5/SAS-Programming/Capturing-data/m-p/582146#M165521.

[sks521]

Here is the data sample

input aekey:$32. patient_id:$32. link_method:$18. procodet:$3. postcode_flag:$5. sex:BEST12. ethnos:$1. activage:BEST12. arrivalage:BEST12. pgpprac:$32. sitetret:$5. aearrivalmode:BEST12. aeattenddisp:BEST12. aerefsource:BEST12. aedepttype:BEST12. aearriva
ldate:YYMMDD10. aearrivaltime:BEST12. diag_scheme:BEST12. diag_01:$6. diag_02:$1. diag_03:$1. diag_04:$1. diag_05:$1. diag_06:$1. diag_07:$1. diag_08:$1. diag_09:$1. diag_10:$1. diag_11:$1. diag_12:$1. invest_01:BEST12. invest_02:BEST12. invest_03:$1. inve
st_04:$1. invest_05:$1. invest_06:$1. invest_07:$1. invest_08:$1. invest_09:$1. invest_10:$1. invest_11:$1. invest_12:$1. treat_01:BEST12. treat_02:BEST12. treat_03:BEST12. treat_04:BEST12. treat_05:$1. treat_06:$1. treat_07:$1. treat_08:$1. treat_09:$1. t
reat_10:$1. treat_11:$1. treat_12:$1. imd15decile_overall:BEST12.;

format sex BEST12. activage BEST12. arrivalage BEST12. aearrivalmode BEST12. aeattenddisp BEST12. aerefsource BEST12. aedepttype BEST12. aearrivaldate YYMMDD10. aearrivaltime BEST12. diag_scheme BEST12. invest_01 BEST12. invest_02 BEST12. treat_01 BEST12.
treat_02 BEST12. treat_03 BEST12. treat_04 BEST12. imd15decile_overall BEST12.;
datalines;
c5f9d619392b7317bff4355d7e0c3829 8e19bf9b06f32ea3538b417a69254e3f NHSNO RCB VALID 1 A . 16 b6a4f27069e741c4c88b4c9975cd46bd RCB55 2 3 1 1 2012-06-21 1602 1                         24 .                     221 11 . .                 3
42bdbdc79100370c0f7b92e8219c950f 2a44ce4db8a93eb5a111d0900a818351 NHSNO RWY VALID 2 A 16 16 6868ed2cc119330c86104685a1100ecb RWY01 2 3 1 1 2012-04-21 231 1 05 15L                       1 1                     511 . . .                 9
dd8c32086cfd590049fdeb9817083967 f21138c84b7ea596bb1aeb4a7cbb1e32 NHSNO RWA VALID 1 A 16 16 f6b0b1b3a917196df5ac8dedbf282b03 RWA00 2 2 1 1 2012-02-28 1123 1 03 25L                       3 24                     291 43 21 12                 3
c810915d6201106c5e9caec870183a21 23f76559afb9135cb92fbcf9072d9957 FULL ID NHSNO LINK RJL VALID 1 A 16 16 V81999 RJL32 2 3 1 1 2012-01-21 1601 1                         24 .                     222 21 236 42                 8
4c8808798cb8278f03540122edb7f1b1 1152df925fcc5d927f5e811485338fc0 NHSNO RP5 VALID 2 A 16 16 8e29e8d20b7b2d694d4251505166202c RP5DR 2 2 8 1 2012-04-11 2115 1 38                       24 .                     99 . . .                 5
4ee99a96e5cdee139f6c7b4a3ec31734 1152df925fcc5d927f5e811485338fc0 NHSNO RP5 VALID 2 A 16 16 8e29e8d20b7b2d694d4251505166202c RP5DR 2 5 1 1 2012-08-16 2029 1                         1 .                     222 51 . .                 5
ab1c30f4ae71acebfc5dc9cff812ae53 3ab4b9ec7420ffa8988cfa496116359c NHSNO RCD VALID 2 A 16 16 eade35a693a9b16e87df763977afb438 RCD00 9 3 8 1 2012-04-07 1933 1                         1 .                     222 . . .                 5
de5e1b9898373a106a4a16ad01cb25c3 50d356d07b3e92e1258e0b35e0cfcc09 NHSNO RP5 VALID 2 A 16 16 50a35e60f27ff4668292dccdfa3e8828 RP5DR 1 2 1 1 2012-07-14 1352 1                         1 2                     25 12 . .                 2
3f4118a4e4c224a7478e4b33bb3e6105 50d356d07b3e92e1258e0b35e0cfcc09 NHSNO RP5 VALID 2 A 16 16 50a35e60f27ff4668292dccdfa3e8828 RP5DR 1 2 8 1 2012-08-15 1110 1                         2 .                     99 . . .                 2
3356a3355f3e59aa331e4bb2e7b1db73 81cff6dca94f53efdc87ac80c051358a NHSNO RXF VALID 1 A 16 16 281134eab2a8eb9965f3d0f15379f550   2 12 1 1 2012-07-03 2303 1                         24 24                     21 . . .                 3
a05d152ecb5bcff3026eb0eefbd76fa0 33e3a293fde99c7243a07269117ce957 NHSNO RCB VALID 1 A . 16 e2f7a5addeb1972e9d0e845dceb4d3de RCB55 2 12 1 1 2012-07-13 1443 1 38                       24 .                     99 . . .                 7
609a673edb687944c4e0dfff7be562c6 4dc07d9b7c449f3979c9619e0e03f1ef NHSNO RHQ VALID 1 9 16 16 72abfa3cf5703c2b01c2c0d1e5b1360c RHQNG 1 3 3 1 2012-02-11 1205 1                         1 24                     22 2 27 .                 4
437074e305e6b5d6018a9c117703778d 52acdd0c3a2cb35bc0613e570ab3be83 NHSNO RFF VALID 2 A 16 16 9ac4b686e8b3dd9506b852b5c4682ddb RFFAA 2 6 1 1 2012-05-12 825 2 S934                       1 .                     53 . . .                 3
437196899e460706d1de42e950db8952 e42f92c9cf6f0d1d5a3250e7f2cd5691 NHSNO RCF VALID 2 A 16 16 X99999 RCF22 2 4 1 1 2012-05-16 835 1 02115L                       1 .                     222 6 . .                 8
5e70c062281194516aaac18411b58bc4 79cfaefb3f9ba109121639dc8585817e NHSNO RHQ VALID 2 A 16 16 9e9d3fca5d485c0192eacadd02ba8eb1 RHQNG 2 3 1 1 2012-06-15 2120 1 38                       24 .                     22 27 . .                 9
bcf368db9ada6eb8b4a224f66f053902 74b4cd470f8e43cb34c9ade6b25566e9 NHSNO RWY VALID 2 A 16 16 0d3b147edbd26a5ceceb6cc4a403fe00 RWY02 2 3 1 1 2012-02-18 1847 1 06 33R                       1 .                     221 . . .                 6
e265db1784b796e856d6ea8cbe9c0f1e 1d5c4e9ea0ac7439ef0e08ea5f8bf8ae NHSNO RXF VALID 2 A 16 16 51c5f674fc8d4674054fc56574548648   2 3 1 1 2012-05-09 2155 1                         24 24                     222 . . .                 9
261c8bc3465fcd38f4aee8a5f52cfc03 a3de746a87058ec679fd92049c70310b NHSNO RCB VALID 1 9 . 16 abbce63ebbfb10a5589cefc7fe32fe9f RCB55 2 3 7 1 2012-03-19 1903 1                         1 .                     222 . . .                 7
c9d397299ada4e8f6b8c6ef45a3dc2cc a3de746a87058ec679fd92049c70310b NHSNO RCB VALID 1 9 . 16 abbce63ebbfb10a5589cefc7fe32fe9f RCB55 2 3 7 1 2012-04-27 1804 1                         1 .                     2 . . .                 7
42b1512bd912e97f0d2fe7432d231703 e1261236926d557c4d868fb451f6b14b NHSNO RP5 VALID 2 A 16 16 060a8a17c6c3799ebf29ab6a649b71f3 RP5BA 2 2 1 1 2012-06-19 1852 1                         24 .                     222 . . .                 7
;;;;

[andreas_lds]

After adding the data-statement, the following notes are in the log:

NOTE: Variable aearrivaldate is uninitialized.
NOTE: Invalid data for aearriva in line 34 139-148.
NOTE: Invalid data for ldate in line 34 150-153.
NOTE: Invalid data for invest_01 in line 35 85-85.
NOTE: Invalid data for inve in line 35 93-124.
NOTE: Invalid data for treat_01 in line 35 160-162.
NOTE: Invalid data for t in line 36 34-65.

So, please fix those issues.

[sks521]

I apologise but can't really tell what's wrong with it.

I am copying the log here,

8    %let dataSetName = sheffield;
9    *number of observations you want to keep;
10   %let obsKeep = 10;
11
12
13   ******************************************************
14   DO NOT CHANGE ANYTHING BELOW THIS LINE
15   ******************************************************;
16
17   %let source_path =
17 ! https://gist.githubusercontent.com/statgeek/bcc55940dd825a13b9c8ca40a904cba9/raw/865d2cf18f5
17 ! 150b8e887218dde0fc3951d0ff15b/data2datastep.sas;
18
19   filename reprex url "&source_path";
20   %include reprex;
181  filename reprex;
NOTE: Fileref REPREX has been deassigned.
182
183  option linesize=max;
184  %data2datastep(dsn=&dataSetName, obs=&obsKeep);
NOTE: No rows were selected.
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


DATA2DATASTEP DSN SHEFFIELD
DATA2DATASTEP FILE create_WORK_SHEFFIELD_data.sas
DATA2DATASTEP FMT YES
DATA2DATASTEP FMTLIST
DATA2DATASTEP INPUTLIST aekey:$8. procodet:$8. sex:32. ethnos:$8. activage:32. arrivalage:32. pgpprac:$8. sitetret:$8. aearrivalmode:32. aeattenddisp:32. aerefsource:32. aedepttype:32. aearrivaldate:32. aearrivaltime:32. diag_scheme:32. diag_01:$8.
diag_02:$8. diag_03:$8. diag_04:$8. diag_05:$8. diag_06:$8. invest_01:$8. invest_02:$8. invest_03:$8. invest_04:$8. invest_05:$8. invest_06:$8. invest_07:$8. invest_08:$8. invest_09:$8. invest_10:$8. invest_11:$8. invest_12:$8. treat_01:32. treat_02:32.
treat_03:32. treat_04:32. treat_05:$8. treat_06:$8. treat_07:$8. treat_08:$8. treat_09:$8. imd15decile_overall:32.
DATA2DATASTEP LBL YES
DATA2DATASTEP LIB WORK
DATA2DATASTEP LS 256
DATA2DATASTEP MEMLABEL
DATA2DATASTEP MSGTYPE NOTE
DATA2DATASTEP OBS 10
DATA2DATASTEP OUTLIB WORK
DATA2DATASTEP SQLEXITCODE 0
DATA2DATASTEP SQLOBS 0
DATA2DATASTEP SQLOOPS 17
DATA2DATASTEP SQLRC 0
DATA2DATASTEP SQLXOBS 0
DATA2DATASTEP SQLXOPENERRS 0
DATA2DATASTEP VARLIST aekey procodet sex ethnos activage arrivalage pgpprac sitetret aearrivalmode aeattenddisp aerefsource aedepttype aearrivaldate aearrivaltime diag_scheme diag_01 diag_02 diag_03 diag_04 diag_05 diag_06 invest_01 invest_02 invest_03
invest_04 invest_05 invest_06 invest_07 invest_08 invest_09 invest_10 invest_11 invest_12 treat_01 treat_02 treat_03 treat_04 treat_05 treat_06 treat_07 treat_08 treat_09 imd15decile_overall
NOTE: The quoted string currently being processed has become more than 262 characters long.  You might have unbalanced quotation marks.
WARNING: Apparent symbolic reference LBLLIST not resolved.

data WORK.SHEFFIELD;
  infile datalines dsd truncover;

input aekey:$8. procodet:$8. sex:32. ethnos:$8. activage:32. arrivalage:32. pgpprac:$8. sitetret:$8. aearrivalmode:32. aeattenddisp:32. aerefsource:32. aedepttype:32. aearrivaldate:32. aearrivaltime:32. diag_scheme:32. diag_01:$8. diag_02:$8. diag_03:$8. d
iag_04:$8. diag_05:$8. diag_06:$8. invest_01:$8. invest_02:$8. invest_03:$8. invest_04:$8. invest_05:$8. invest_06:$8. invest_07:$8. invest_08:$8. invest_09:$8. invest_10:$8. invest_11:$8. invest_12:$8. treat_01:32. treat_02:32. treat_03:32. treat_04:32. t
reat_05:$8. treat_06:$8. treat_07:$8. treat_08:$8. treat_09:$8. imd15decile_overall:32.;
datalines;
e81e201e RXF 1 A 2 73 da4863e9   2 3 1 1 19518 1014 1             24 24                     34 41 . .           3
5221b6af RXF 2 J 6 82 37d3d778   2 3 1 1 19537 1359 1             1 24                     221 222 . .           3
c5f9d619 RCB 1 A . 16 b6a4f270 RCB55 2 3 1 1 19165 1602 1             24                       221 11 . .           3
42bdbdc7 RWY 2 A 16 16 6868ed2c RWY01 2 3 1 1 19104 231 1 05 15L           1 1                     511 . . .           9
b43c9c60 RXF 2 A 15 21 b9e9dcb3   2 1 1 1 19526 1423 1             24                       21 511 . .           4
dd8c3208 RWA 1 A 16 16 f6b0b1b3 RWA00 2 2 1 1 19051 1123 1 03 25L           3 24                     291 43 21 12           3
c810915d RJL 1 A 16 16 V81999 RJL32 2 3 1 1 19013 1601 1             24                       222 21 236 42           8
4c880879 RP5 2 A 16 16 8e29e8d2 RP5DR 2 2 8 1 19094 2115 1 38           24                       99 . . .           5
4ee99a96 RP5 2 A 16 16 8e29e8d2 RP5DR 2 5 1 1 19221 2029 1             1                       222 51 . .           5
ab1c30f4 RCD 2 A 16 16 eade35a6 RCD00 9 3 8 1 19090 1933 1             1                       222 . . .           5
;;;;
NOTE: There were 10 observations read from the data set WORK.SHEFFIELD.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

[andreas_lds]

Except for variable "aekey" all other variables are empty.

[sks521]

Yes, I can see this warning sign;

NOTE: The quoted string currently being processed has become more than 262 characters long.  You might have unbalanced quotation marks.
WARNING: Apparent symbolic reference LBLLIST not resolved.

but not sure what that means and how to correct it 

[sks521]

I have changed the syntax as per Kurt's advice and re-run the programme. 

Here is the sample data. Not sure if it would work OK though!

data WORK.ORIGINAL;
  infile datalines dsd truncover;

input procodet:$3. sex:BEST12. ethnos:$1. activage:BEST12. arrivalage:BEST12. aearrivalmode:BEST12. aeattenddisp:BEST12. aerefsource:BEST12. aedepttype:BEST12. aearrivaldate:YYMMDD10. diag_scheme:BEST12. diag_01:$6. diag_02:$1. diag_03:$1. diag_04:$1. diag
_05:$1. diag_06:$1. treat_01:BEST12. treat_02:BEST12. treat_03:BEST12. treat_04:BEST12. treat_05:$1. treat_06:$1. treat_07:$1. treat_08:$1. treat_09:$1. imd15decile_overall:BEST12.;

format sex BEST12. activage BEST12. arrivalage BEST12. aearrivalmode BEST12. aeattenddisp BEST12. aerefsource BEST12. aedepttype BEST12. aearrivaldate YYMMDD10. diag_scheme BEST12. treat_01 BEST12. treat_02 BEST12. treat_03 BEST12. treat_04 BEST12. imd15de
cile_overall BEST12.;
datalines;
RXF 1 A 2 73 2 3 1 1 2013-06-09 1             34 41 . .           3
RXF 2 J 6 82 2 3 1 1 2013-06-28 1             221 222 . .           3
RCB 1 A . 16 2 3 1 1 2012-06-21 1             221 11 . .           3
RWY 2 A 16 16 2 3 1 1 2012-04-21 1 05 15L           511 . . .           9
RXF 2 A 15 21 2 1 1 1 2013-06-17 1             21 511 . .           4
RWA 1 A 16 16 2 2 1 1 2012-02-28 1 03 25L           291 43 21 12           3
RJL 1 A 16 16 2 3 1 1 2012-01-21 1             222 21 236 42           8
RP5 2 A 16 16 2 2 8 1 2012-04-11 1 38           99 . . .           5
RP5 2 A 16 16 2 5 1 1 2012-08-16 1             222 51 . .           5
RCD 2 A 16 16 9 3 8 1 2012-04-07 1             222 . . .           5
;;;;

[Kurt_Bremser]

Seems the macro does not correctly set the delimiter, and you need to concatenate code lines:

data WORK.ORIGINAL;
  infile datalines dsd dlm=' ' truncover;
input procodet:$3. sex:BEST12. ethnos:$1. activage:BEST12. arrivalage:BEST12. aearrivalmode:BEST12. aeattenddisp:BEST12. aerefsource:BEST12. aedepttype:BEST12. aearrivaldate:YYMMDD10. diag_scheme:BEST12. diag_01:$6. diag_02:$1. diag_03:$1. diag_04:$1. diag_05:$1. diag_06:$1. treat_01:BEST12. treat_02:BEST12. treat_03:BEST12. treat_04:BEST12. treat_05:$1. treat_06:$1. treat_07:$1. treat_08:$1. treat_09:$1. imd15decile_overall:BEST12.;
format sex BEST12. activage BEST12. arrivalage BEST12. aearrivalmode BEST12. aeattenddisp BEST12. aerefsource BEST12. aedepttype BEST12. aearrivaldate YYMMDD10. diag_scheme BEST12. treat_01 BEST12. treat_02 BEST12. treat_03 BEST12. treat_04 BEST12. imd15decile_overall BEST12.;
datalines;
RXF 1 A 2 73 2 3 1 1 2013-06-09 1             34 41 . .           3
RXF 2 J 6 82 2 3 1 1 2013-06-28 1             221 222 . .           3
RCB 1 A . 16 2 3 1 1 2012-06-21 1             221 11 . .           3
RWY 2 A 16 16 2 3 1 1 2012-04-21 1 05 15L           511 . . .           9
RXF 2 A 15 21 2 1 1 1 2013-06-17 1             21 511 . .           4
RWA 1 A 16 16 2 2 1 1 2012-02-28 1 03 25L           291 43 21 12           3
RJL 1 A 16 16 2 3 1 1 2012-01-21 1             222 21 236 42           8
RP5 2 A 16 16 2 2 8 1 2012-04-11 1 38           99 . . .           5
RP5 2 A 16 16 2 5 1 1 2012-08-16 1             222 51 . .           5
RCD 2 A 16 16 9 3 8 1 2012-04-07 1             222 . . .           5
;;;;

I guess it's time for one of us to take care of the little problems in the macro.

[Kurt_Bremser]

The message

NOTE: The quoted string currently being processed has become more than 262 characters long.

happens because of the large number of variables in your dataset.

WARNING: Apparent symbolic reference LBLLIST not resolved.

happens because you do not have any labels in your dataset, but the macro tries to include them by default.

Use the proper option to suppress this:

%data2datastep(dsn=&dataSetName, obs=&obsKeep,lbl=N);

[Tom]

This discussion seems to have devolved into a discussion of the limitations of the link that many people send to help users convert their data into data steps. If you want to keep playing with that try using this tool instead:  https://github.com/sasutils/macros/blob/master/ds2post.sas

Getting back to the original question.  You should approach the issue in pieces. Create Boolean values that evaluate the different parts of your requirement first. Then use those to implement your sub-setting.

data want;
  set have;
  array dx diag_01-diag_06;
  array tx treat_01-treat_12;
  any_dx=0;
  any_tx=0;
  any_j=0;
  do i=1 to dim(dx) while(not any_dx);
    any_dx=dx[i] in ('251','J441','J450','J45','J451','J458','J459','J46','J46X','R062');
  end;
  do i=1 to dim(tx) while(not any_tx);
    any_tx=tx[i] in  ('25');
  end;
  do i=1 to dim(dx) while(not any_j);
    any_j=dx[i] in ('J21','J210','J218','J219','J05','J050');
  end;
  if any_dx or (any_tx and not any_j);
run;