Programming the statistical procedures from SAS

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

View solution in original post


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:

 

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;

 

 

Attachment
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

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

Posted in reply to novinosrin

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 177 views
  • 1 like
  • 4 in conversation