## How can I create a dataset with all unique values of over 100 variables?

Solved
Super Contributor
Posts: 1,636

# How can I create a dataset with all unique values of over 100 variables?

Dear All,

How should I get what I want?

Thank you very much for your input!

data have;
input (a b c) (\$);
cards;
dd ee ff
ff hh gg
ss dd hh
;

observations in the dataset I want:

dd
ee
ff
hh
gg
ss

Accepted Solutions
Solution
‎05-21-2013 02:30 PM
Posts: 5,540

## Re: How can I create a dataset with all unique values of over 100 variables?

Hi Linlin,

Use a variable list a--c to refer to your 100 variables (assuming they are listed consecutively) :

data have;
input (a b c) (\$);
cards;
dd ee ff
ff hh gg
ss dd hh
;
data list;
set have;
array _a{*} a--c;
do i = 1 to dim(_a);
value = _a{i};
output;
end;
run;

proc sql;
create table uniqueValues as
select unique value from list;
select * from uniqueValues;
quit;

PG

PG

All Replies
Super User
Posts: 8,120

## Re: How can I create a dataset with all unique values of over 100 variables?

Rotate the data into a single column and let proc sort/FREQ/SQL find the distinct values.

data middle / view=middle;

set have;

array _all a b c;

do over _all;  value = _all; output; end;

run;

proc freq data=middle ;

tables value / out=want missing;

run;

Posts: 3,167

## Re: How can I create a dataset with all unique values of over 100 variables?

Or Hash():

data have;

input (a b c) (\$);

cards;

dd ee ff

ff hh gg

ss dd hh

;

data _null_;

if _n_=1 then do;

declare hash h();

h.definekey('newvar');

h.definedata('newvar');

h.definedone();

end;

set have end=last;

array t _character_;

length newvar \$ 8;

call missing(newvar);

do over t;

rc=h.replace(key:t, data:t);

end;

if last then rc=h.output(dataset:'want');

run;

Haikuo

Solution
‎05-21-2013 02:30 PM
Posts: 5,540

## Re: How can I create a dataset with all unique values of over 100 variables?

Hi Linlin,

Use a variable list a--c to refer to your 100 variables (assuming they are listed consecutively) :

data have;
input (a b c) (\$);
cards;
dd ee ff
ff hh gg
ss dd hh
;
data list;
set have;
array _a{*} a--c;
do i = 1 to dim(_a);
value = _a{i};
output;
end;
run;

proc sql;
create table uniqueValues as
select unique value from list;
select * from uniqueValues;
quit;

PG

PG
Super Contributor
Posts: 1,636

## Re: How can I create a dataset with all unique values of over 100 variables?

Hi Tom, Haikuo, and PG:

Thank you very much for your help!!! - Linlin

Contributor
Posts: 32

## Re: How can I create a dataset with all unique values of over 100 variables?

Wow, three excellent ways to solve the problem!

🔒 This topic is solved and locked.