pulling specific data from many data stored in a single excell cell

[Barkat]

An Excell sheet has two columns. Name and blood test history. In the blood test history column there are multiple test results with date and source of blood.

Name                       blood test history

ABC                          5/23/2017 9.6 (venous)

                                 8/2/2017 7.6 (capilary)

                                 12/13/2017 10.5 (venous)

                                 4/20/2018 12.6 (venous)

DEF                          7/20/2017 10.6 (venous)

                                 9/22/2017 27.9 (capilary)

                                 11/3/2017 15.7 (venous)

                                 3/10/2018 9.9 (venous)

XYZ                          6/20/2017 7.7 (venous)

                                 8/22/2017 7.9 (capilary)

                                 10/3/2017 9.7 (venous)

                                 1/10/2018 5.9 (venous)

How do I pull only the test results (with date and source) less or greater than a specific value and from a specific source, and then create a new table/dataset? As for example I would like to create list of names who have test result more than 10.0 and source is venous. So the output table will be as below

Name                       blood test history

ABC                         12/13/2017 10.5 (venous)

DEF                          7/20/2017 10.6 (venous)

                                 11/3/2017 15.7 (venous)

[Reeza]

If you have a question you'll need to post more information. Specifically, what do you have, what do you want and what's the logic? Ideally, you could also include what approaches you've tried so we don't suggest solutions you've already tried.

If you're trying to parse data, look into SCAN() or regex.

[AhmedAl_Attar]

Have a look at @ChrisHemedinger 2015 Blog

Look for "Sometimes you need just one value from a spreadsheet. That's a common use case for dynamic data exchange (DDE), which isn't as feasible as it once was. With the XLSX engine, you can use FIRSTOBS= and OBS= options to control how much data you retain:"

Ahmed

[AhmedAl_Attar]

Check this 

libname myxlsx xlsx "/folders/myfolders/sampleBook.xlsx";
data want(drop=curr:);
	do _n_=1 by 1 until(eof);
		set myxlsx.Sheet1 end=eof;
		retain curr_name;
		if (name NE '') then
		curr_name=name;
		if (_n_ in (3,5,7)) then
		do;
			name=curr_name;
			output;
		end;
    end;
run;  

[Patrick · Accepted solution]

You will likely have to adjust below code a bit to fully function with your real data but it demonstrates the approach.

Data have represents your data read into SAS 1:1 from the Excel sheet, data want represents your data transformed into an easy to use form. Once you've got that row selection of specific dates or the like becomes simple.

data have;
  infile datalines dsd dlm=',' truncover;
  input name :$20. blood_test_history :$100.;
  datalines;
ABC,5/23/2017 9.6 (venous)
,8/2/2017 7.6 (capilary)
,12/13/2017 10.5 (venous)
,4/20/2018 12.6 (venous)
DEF,7/20/2017 10.6 (venous)
,9/22/2017 27.9 (capilary)
,11/3/2017 15.7 (venous)
,3/10/2018 9.9 (venous)
;


data want(drop=_:);
  set have;

  if 0 then _name2=name;
  retain _name2;
  _name2=coalescec(name,_name2);
  name=_name2;

  format test_dt date9.;
  test_dt=input(scan(blood_test_history,1,' '),mmddyy10.);

  format test_result best32.;
  test_result=input(scan(blood_test_history,2,' '),best32.);

  length test_type $20;
  test_type=scan(blood_test_history,-2,'()');

run;

proc print;
run;

[Reeza]

When you import the data is that how it's coming into SAS? I'm curious as to how it's getting read in by default and I suspect it's not that though it could definitely be. Post the SAS data set to match this if you can.

[Kurt_Bremser]

SAS coding is purely data-driven, so we need data to work with.

Either supply the Excel file and the code used to import it into SAS, or supply the dataset from the import in a data step with datalines (see my footnotes). Right now it is not obvious (among other things) if the "blood test history" values are contained in one variable in one dataset observation (separated by line breaks), or spread across several observations.