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

[emaneman]

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

[Reeza]

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)

[emaneman]

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:

FILENAME REFFILE '/folders/myfolders/Bergamo2018/testing ART reading.csv';
libname bergamo '/folders/myfolders/Bergamo2018/';

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

PROC CONTENTS DATA=WORK.IMPORT1; RUN;

[art297]

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

[emaneman]

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?

[art297 · Accepted solution]

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

[emaneman]

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

E

[novinosrin]

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,...

[emaneman]

the numbers correspond to choices participants in a study made, when presented with a list of 120 items. 60 belong to a category, the other 60 to another category. Because of poor programming of the study, the data came organized this way.