BookmarkSubscribeRSS Feed
WilliamB
Obsidian | Level 7

Hello,

 

I have a first table:

 

Name C1

 

Thomas 5

To 5

Fab 4

Fabien 4

Nelson 9

 

2 nd table 

 

Fabien 

Thomas

Nelson

 

 

I would like to create a 3rd table that brings together individuals with the same values of 'C1'.
But that the value of 'Name' is the one present in table 2.

 

I would like to get this:

 

Name C1 Count

 

Fabien 5  2

Thomas 4 2

Nelson 9 1

 

 

I do not want to rename the values of name.
And I would like to have a variable count that counts the number of times that the individual appeared
Example: Fabien and Fab count = 2

 

Thanks for your help

 

9 REPLIES 9
WilliamB
Obsidian | Level 7

Hello,

I have a first SAS table

 

Name C1 

 

Thomas 2 

Fabien 7

Nelson 9

 

 

2nd Sas table

 

Name R1

 

Thomas TB

Fabien AB

Didier Ajac

 

 

 

I would like to create a 3rd table, which shows the individuals in table 1 who are present in table 2, and concatenate the characteristics. But keep all individuals from table 2 even if they are not present in table 1. 

If possible in proc SQL. Thank you

 

I want to get this

 

Name C1 R1

 

Thomas 2 TB

Fabien 7   AB

Didier .   Ajac

 

 

Thanks for your help

novinosrin
Tourmaline | Level 20

left join using table2 as left table on name

 

data table1;
input (Name C1) ($);
datalines;
Thomas 2 
Fabien 7
Nelson 9
;

data table2;
input (Name r1) ($);
datalines;
Thomas TB
Fabien AB
Didier Ajac
;

proc sql;
create table want as
select a.*,c1, catx(' ',r1, c1) as concat_chars_for_op
from table2 a left join table1 b
on a.name=b.name
order by name desc;
quit;
novinosrin
Tourmaline | Level 20
data table1;
input (Name C1) ($);
datalines;
Thomas 2 
Fabien 7
Nelson 9
;

data table2;
input (_Name r1) ($);
datalines;
Thomas TB
Fabien AB
Didier Ajac
;



proc sql;
create table want as
select a.*,cnt+1 as count
from table2 a left join (select Name,count(name) as cnt from table1 group by name) b
on a._name=b.name;
quit;
rajeshalwayswel
Pyrite | Level 9


data table1;
input (_Name C1) ($);
datalines;
Thomas 2
Fabien 7
Nelson 9
;

data table2;
input (_Name r1) ($);
datalines;
Thomas TB
Fabien AB
Didier Ajac
;
run;

proc sort data=table1;by _name;run;

proc sort data=table2;by _name;run;

data want;
merge table1(in=a) table2(in=b);
by _name;
if a;
if a and b then count=2;
proc sort ;by _name;
run;

WilliamB
Obsidian | Level 7

 

 

Sas Table

Hello,

I have a first SAS table

 

Name C1

 

Thomas 1

Tho 1

Fabien 2

Fab   2

 

I would like to create a program where if C1 (n) = C1 (n-1) from the observation before then Name (n ) = Name (n-1)

 

I want to get this

 

 

Name C1 

Thomas 1

Thomas 1

Fabien 2

Fabien 2

 

I have a very large table, so I can not rename individually any observation.

 

 

Thanks for your help

RW9
Diamond | Level 26 RW9
Diamond | Level 26

This has nothing to do with renaming variables.  Variables are the columns, observations are the elements.  What you are looking at is coding the observations you have.  You can do this in a number of ways, proc format for instance, retain first value down.  My personal preference would be to create a decode table, a smaller table containing distinct c1 and name, as they should be fully, then joining this table on when needed.  E.g.

/* Note I do this step so I get the longest name, I assume this is your logic */
data inter;
  set have;
  l=lengthn(name);
run;

proc sort data=inter;
  by c1 name descending l;
run;

proc sort data=inter nodupkey;
  by c1 name;
run;

/* Now use it to merge on */
data want;
  merge have inter (rename=(name=full_name));
  by c1;
run;
Reeza
Super User

You can use RETAIN and FIRST logic to keep the first value. The number of rows don't matter but the data does need to be in the order specified, similar to your demo data.

 

 

delete_retain example.JPG

Ksharp
Super User
data t1;
input Name $ C1;
cards;
Thomas 5
To 5
Fab 4
Fabien 4
Nelson 9
;
run;

 

data t2;
input name $;
cards; 
Fabien 
Thomas
Nelson
;
run;
proc sql;
create table want as
 select a.*,b.c1,b.n
  from t2 as a left join
(select *,count(*) as n from t1 group by c1 having length(name)=max(length(name))) as b
   on a.name=b.name;
quit;
Reeza
Super User

@WilliamB please do not post the same question multiple times. I've merged your three posts into one.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 9 replies
  • 1073 views
  • 1 like
  • 6 in conversation