topic Re: Keep variable values across rows in SAS Programming

Rename and gather people through another table

WilliamB — Mon, 15 Jan 2018 08:20:12 GMT

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

Gathering information

WilliamB — Sun, 14 Jan 2018 23:14:56 GMT

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

Re: Gathering information

novinosrin — Sun, 14 Jan 2018 23:27:05 GMT

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;

Re: Rename and gather people through another table

novinosrin — Sun, 14 Jan 2018 23:38:02 GMT

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;

Re: Rename and gather people through another table

rajeshalwayswel — Mon, 15 Jan 2018 00:17:26 GMT

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;

Keep variable values across rows

WilliamB — Mon, 15 Jan 2018 15:52:48 GMT

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

Fabien 2

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

Thanks for your help

Re: Automatically rename

RW9 — Mon, 15 Jan 2018 09:01:17 GMT

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;

Re: Rename and gather people through another table

Ksharp — Mon, 15 Jan 2018 12:54:19 GMT

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;

Re: Keep variable values across rows

Reeza — Mon, 15 Jan 2018 16:05:14 GMT

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.

Re: Rename and gather people through another table

Reeza — Mon, 15 Jan 2018 16:45:32 GMT

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