topic Re: how to read multiple numbers as separate values in the same variable in Statistical Procedures

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

emaneman — Fri, 04 May 2018 22:35:06 GMT

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

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

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

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

Reeza — Fri, 04 May 2018 22:40:44 GMT

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)

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

art297 — Fri, 04 May 2018 22:46:44 GMT

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

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

emaneman — Fri, 04 May 2018 23:02:33 GMT

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;

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

emaneman — Fri, 04 May 2018 23:07:42 GMT

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?

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

art297 — Fri, 04 May 2018 23:12:55 GMT

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

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

novinosrin — Fri, 04 May 2018 23:13:34 GMT

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

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

emaneman — Fri, 04 May 2018 23:16:43 GMT

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

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

emaneman — Sat, 05 May 2018 01:10:03 GMT

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.