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)
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;
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
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.