topic How to group variables and count based on groups in SAS Enterprise Guide

How to group variables and count based on groups

miss2223 — Tue, 04 Sep 2018 03:21:30 GMT

data Single;
input Single $10.;
cards;
a
b
c
;

data Multiple;
input Multiple $10.;
cards;
D
e
f
g
h
i
;

I want input value in 'single' counted 1 account
output for single
Single Account
a 1
b 1
c 1

For multiple, because d&e is one account, f&g&h&i is one account
output i want for multiple is
Multiple Account
d&e 1
f&g&h&i 1

I dont mine putting them together in same data set, I just want to make sure the combination is correct for it to count the correct account numbers.

Re: How to group variables and count based on groups

PeterClemmensen — Tue, 04 Sep 2018 07:05:18 GMT

How do we know that d&e are one account from your data? Please be more specific.

Re: How to group variables and count based on groups

ballardw — Tue, 04 Sep 2018 15:25:05 GMT

@miss2223 wrote:

data Single;
input Single $10.;
cards;
a
b
c
;

data Multiple;
input Multiple $10.;
cards;
D
e
f
g
h
i
;

I want input value in 'single' counted 1 account
output for single
Single Account
a 1
b 1
c 1

For multiple, because d&e is one account, f&g&h&i is one account
output i want for multiple is
Multiple Account
d&e 1
f&g&h&i 1

I dont mine putting them together in same data set, I just want to make sure the combination is correct for it to count the correct account numbers.

Please be careful with providing "example" data and using different values. You example has capital D but you refer to d&e. The values would actually be different in terms of code in general.

And I will emphasize @PeterClemmensen's comment that there is nothing in the data shown that indicates D and e should be considered as one anything or that f, g, h and I (sorry the forum capitalizes single letter I ) are another group. There really needs to be some logic, rule or a reference data set.

If there is no reference but your personal knowledge then you might consider a custom format:

proc format library=work;
value $acct
'a'='a'
'b'='b'
'c'='c'
'd','e'='d & e'
'f','g','h','i' = 'f & g & h & i'
;
run;
data Single;
input Single $10.;
cards;
a
b
c
;
data Multiple;
input Multiple $10.;
cards;
d
e
f
g
h
i
;
run;

proc freq data=single;
   tables single;
   format single $acct.;
run;
proc freq data=multiple;
   table multiple;
   format multiple $acct.;
run;

Please not that I changed the value from "D" to "d" to match your output.

Formats are case and spelling sensitive for display values, so attention to detail when creating them is important. If you actually have different spellings that should be treated the same they would all go on the same definition line as in the "multiple" values in the example format.

Also note pasting example code into a code box opened using the forum's {I} icon. Code box entered (or pasted) code is less likely to be reformatted by the forum and is easier to copy and paste into an editor to run.

Re: How to group variables and count based on groups

miss2223 — Wed, 05 Sep 2018 02:57:05 GMT

Seems like I didnt make it clear on my question.

Lets say

/* Independent item

data Single;
input Single $10.;
cards;

Toyota
Cloud

/* Substitution item

data Multiple;
input Multiple $10.;
cards;
Berry
Pen
Paper
English

Orange

Spanish
;

OUTPUT to appear

Single Output

Toyota 1
Cloud 1

/* As Berry and Orange they both belongs to the group of Fruits, I want the output to appear as 1

/* Pen and paper is a group

/* English and Spanish is in a group

Multiple Outout

Berry & Orange 1

Pen & Paper 1

English & Spanish 1

I hope it makes it clear. Please help 🙂

Thanks