BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
emaneman
Quartz | Level 8

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

8 REPLIES 8
Reeza
Super User
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
Quartz | Level 8

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
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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
Quartz | Level 8

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

E

novinosrin
Tourmaline | Level 20

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
Quartz | Level 8

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

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