DATA Step, Macro, Functions and more

Rename and gather people through another table

Reply
Contributor
Posts: 33

Rename and gather people through another table

[ Edited ]

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

 

PROC Star
Posts: 1,357

Re: Rename and gather people through another table

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;
Frequent Contributor
Posts: 83

Re: Rename and gather people through another table


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;

Super User
Posts: 10,623

Re: Rename and gather people through another table

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;
Contributor
Posts: 33

Gathering information

[ Edited ]

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

PROC Star
Posts: 1,357

Re: Gathering information

[ Edited ]

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;
Contributor
Posts: 33

Keep variable values across rows

[ Edited ]

 

 

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

Super User
Super User
Posts: 9,227

Re: Automatically rename

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;
Super User
Posts: 22,874

Re: Keep variable values across rows

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

Super User
Posts: 22,874

Re: Rename and gather people through another table

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

Ask a Question
Discussion stats
  • 9 replies
  • 203 views
  • 1 like
  • 6 in conversation