## how to read multiple numbers as separate values in the same variable

Solved
Occasional Contributor
Posts: 11

# how to read multiple numbers as separate values in the same variable

Hello, and thank you in advance to those who will try to help!

I have the following issue:

I have an excel file with a variable that contains numbers (range 0-120). Each cell can contain multiple numbers, and they are separated by a comma. E.g. 2,19,4,45,2,9....

For this variable, respondents can have a missing value, or any number of numbers

2,19,4,45,2,9

3, 34, 4

9

17, 3,67,88

I need to read/import this variable so to compute two new variables. The first variable is a count of certain number, let's say "odd" numbers"; the second variable, "even" number. For instance, from the example above, I want to have these two new variables:

3 3

1 2

1

3 1

This is because <2,19,4,45,2,9> has three odd and three even numbers; <3, 34, 4> has one odd and two even numbers, etc.

Can anyone suggest how I could achieve this in SAS?

Thank you

Emanuele

Accepted Solutions
Solution
‎05-04-2018 09:14 PM
PROC Star
Posts: 8,169

## Re: how to read multiple numbers as separate values in the same variable

Not really! Unless you mean:

``````data have;
input fieldname \$20.;
cards;
2,19,4,45,2,9
3, 34, 4
9
17, 3,67,88
;

data want (drop=i);
set have;
v1=0;
v2=0;
do i=1 to countw(fieldname);
if scan(fieldname,i) in (1, 4, 8, 65, 89) then v1+1;
else v2+1;
end;
run;
``````

Art, CEO, AnalystFinder.com

All Replies
Super User
Posts: 23,778

## Re: how to read multiple numbers as separate values in the same variable

1. Import data as standard (PROC IMPORT)
2. Split out data into individual elements (COUNTW() and SCAN())
3. classify as odd/even (MOD() function)
4. Summarize within a report (PROC FREQ)
Occasional Contributor
Posts: 11

## Re: how to read multiple numbers as separate values in the same variable

Reeza, thank you for the incredibly fast response.

I am still a bit lost, since I have never used either functions. If you could give me more details, I would really appreciate.

I understand if you cannot.  I attach the data file that I obtain after importing my data in sas, with the following code:

libname bergamo '/folders/myfolders/Bergamo2018/';

PROC IMPORT DATAFILE=REFFILE
DBMS=CSV
OUT=bergamo.IMPORT1;
GETNAMES=YES;
RUN;

PROC CONTENTS DATA=WORK.IMPORT1; RUN;

PROC Star
Posts: 8,169

## Re: how to read multiple numbers as separate values in the same variable

I think that the following does what you want:

``````data have;
input fieldname \$20.;
cards;
2,19,4,45,2,9
3, 34, 4
9
17, 3,67,88
;

data want (drop=i);
set have;
odd=0;
even=0;
do i=1 to countw(fieldname);
if mod(scan(fieldname,i),2) then odd+1;
else even+1;
end;
run;

``````

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 11

## Re: how to read multiple numbers as separate values in the same variable

Thank you Art297.

This doesn't quite do it. I need to create two new variables. For simplicity, in my first post in indicated that V1 should be the count of even numbers, and V2 the count of odd numbers in the original variable. In reality, It is a bit more complicated:

V1 should be the count of these numbers 1, 4, 8, 65, 89

V2 should be the count of these numbers 2, 3 6, 7,...

Does it make sense?

Solution
‎05-04-2018 09:14 PM
PROC Star
Posts: 8,169

## Re: how to read multiple numbers as separate values in the same variable

Not really! Unless you mean:

``````data have;
input fieldname \$20.;
cards;
2,19,4,45,2,9
3, 34, 4
9
17, 3,67,88
;

data want (drop=i);
set have;
v1=0;
v2=0;
do i=1 to countw(fieldname);
if scan(fieldname,i) in (1, 4, 8, 65, 89) then v1+1;
else v2+1;
end;
run;
``````

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 11

## Re: how to read multiple numbers as separate values in the same variable

This is PERFECT. I am truly amazed at your kindness, and competence!

E

PROC Star
Posts: 1,845

## Re: how to read multiple numbers as separate values in the same variable

Can you please explain how and why you got the below:

V1 should be the count of these numbers 1, 4, 8, 65, 89

V2 should be the count of these numbers 2, 3 6, 7,...

Occasional Contributor
Posts: 11