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

Thank you in advance!

My code:

data have;
input id word $6.;
cards;
1 Butter
1 Butter
1 Arm
2 Pole
2 Pole
2 Arm
2 Train
;
run;

proc sql;
  create table t1 as
    select id, count(id) as n1
   from have
     group by id;
  create table t2 as
    select id, count (id),word as n2 from
    have group by id,word;
  create table t3 as
    select id,count(id) as n3 from t2
    group by id;
  select t1.id,n1,n3 from t1,t3
    where t1.id=t3.id;
quit;

      id        n1        n3

       1         3         2

       2         4         3

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Check table t2, I don't think it is what you expected... anyway, you seem to want for each ID, the total number of words and the number of distinct words. You can indeed get that in one SQL step :

proc sql;

select id, count(word) as totalWords, count(distinct word) as distinctWords

from have

group by id;

PG

PG

View solution in original post

5 REPLIES 5
PGStats
Opal | Level 21

Check table t2, I don't think it is what you expected... anyway, you seem to want for each ID, the total number of words and the number of distinct words. You can indeed get that in one SQL step :

proc sql;

select id, count(word) as totalWords, count(distinct word) as distinctWords

from have

group by id;

PG

PG
JasonDiVirgilio
Quartz | Level 8

Nice! I've never seen the "count(distinct word)" syntax before!

Haikuo
Onyx | Level 15

Of course the following one-step data step approach is not as robust as PG's SQL solution, as it requires same word cluster together within the same id:

data want; 

   do until (last.word);

     set have;

        by id word notsorted; 

        if first.id then call missing(n1,n2);

         n1+1;

    end;

    n2+1;

  if last.id then output;

run;

Haikuo

Peter_C
Rhodochrosite | Level 12

Haikuo

is almost at the optimal solution

Perhaps this might work for ordered data

Data want ;

N1=0;

  DO N2=1 by 1 UNTIL( last.ID );

    set have ;

     BY ID WORD;

     N1 + last.WORD ;

  END ;

run;

Of course it n

Message was edited by: Peter Crawford don't know why

HG
Calcite | Level 5 HG
Calcite | Level 5

Thank you very much!!!Smiley Happy

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 958 views
  • 3 likes
  • 5 in conversation