DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

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
Respected Advisor
Posts: 4,651

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

View solution in original post


All Replies
Super User
Super User
Posts: 6,500

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;

Respected Advisor
Posts: 3,124

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
Respected Advisor
Posts: 4,651

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 helpSmiley HappySmiley HappySmiley Happy!!! - 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.

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

Discussion stats
  • 5 replies
  • 404 views
  • 9 likes
  • 5 in conversation