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
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
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;
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;
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;
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
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;
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.
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;
@WilliamB please do not post the same question multiple times. I've merged your three posts into one.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.