topic Data Extraction in SAS Programming

Data Extraction

RAVI2000 — Tue, 11 May 2021 19:29:59 GMT

I have a variable which contains values like below:

4.9 x 10(6)/kg
2.51 x 10(6)/kg
7.54 x 10(6) / kg
1.90 x 10(6)/kg
2.23 x 10(6)/kg
2.29 x 10^6
2.51 x 10(6)/kg
2.55 x 10(6)/kg
2.58 x 10(6)/kg
2.96 x 10(6)/kg
2.98 x 10(6)/kg
3.06 x 10^(6) cells/kg
3.14 x 10(6)/kg
3.96 x 10 (6)
4.38 x 10(6)/kg
4.60 x 10(6)/kg
4.9 x 10(6)/kg
5.12 x 10^(6) cells/kg
5.13 x 10(6)

I want the only numeric data like

4.9

2.51

7.54

I tried using scan, count, countw, find, index. But sas is unable to capture the (x,*, blank). Any inputs appreciated.

Re: Data Extraction

Reeza — Tue, 11 May 2021 20:05:22 GMT

From your shown data SCAN() would work, so I suspect not all your data is as shown then? Can you elaborate on how SCAN() does not work?

Your statement: But sas is unable to capture the (x,*, blank).
Your output data however shows none of that information, so what exactly do you want?

I would assume one of these would work:

x = scan(variable, 1);
y = scan(variable, 1, "/");

Re: Data Extraction

mklangley — Tue, 11 May 2021 20:08:05 GMT

Is this what you're looking for?

data have;
    input string $32.;
    datalines;
4.9 x 10(6)/kg
2.51 x 10(6)/kg
7.54 x 10(6) / kg
1.90 x 10(6)/kg
2.23 x 10(6)/kg
2.29 x 10^6
2.51 x 10(6)/kg
2.55 x 10(6)/kg
2.58 x 10(6)/kg
2.96 x 10(6)/kg
2.98 x 10(6)/kg
3.06 x 10^(6) cells/kg
3.14 x 10(6)/kg
3.96 x 10 (6)
4.38 x 10(6)/kg
4.60 x 10(6)/kg
4.9 x 10(6)/kg
5.12 x 10^(6) cells/kg
5.13 x 10(6)
;
run;

data want;
    set have;
    number = input(substr(string, 1, find(string, 'x', 1)-1), best.);
run;

Re: Data Extraction

ballardw — Tue, 11 May 2021 21:17:42 GMT

@Reeza wrote:
From your shown data SCAN() would work, so I suspect not all your data is as shown then? Can you elaborate on how SCAN() does not work?

Your statement: But sas is unable to capture the (x,*, blank).
Your output data however shows none of that information, so what exactly do you want?

I would assume one of these would work:

x = scan(variable, 1);
y = scan(variable, 1, "/");

Because the decimal is a default delimiter in SCAN I think to get the "number" part you want

x = scan(variable, 1,' '); to force just getting the first bit of numbers.

And to get a numeric value:

x = input (scan(variable,1,' '),f8.);