topic Re: pulling specific data from many data stored in a single excell cell in New SAS User

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

Barkat — Tue, 30 Jun 2020 17:17:43 GMT

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)

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

Reeza — Tue, 30 Jun 2020 17:04:09 GMT

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.

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

AhmedAl_Attar — Tue, 30 Jun 2020 22:56:40 GMT

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

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

Patrick — Wed, 01 Jul 2020 01:32:10 GMT

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;

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

AhmedAl_Attar — Wed, 01 Jul 2020 01:04:01 GMT

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;

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

Reeza — Wed, 01 Jul 2020 02:16:13 GMT

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.

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

Kurt_Bremser — Wed, 01 Jul 2020 06:27:30 GMT

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.