topic Re: Combine multiple rows of character data in SAS Programming

Combine multiple rows of character data

Huijing — Tue, 04 Feb 2020 01:12:59 GMT

Hi, I have the following source data that need to be combined and wonder if anyone could provide some thoughts on how to do it. The source data is:

Policy Var1 Var2 Var3

1234 SP500

1234 NASDAQ

1234 Russell

I want to have it look like the following:

Policy Var1 Var2 Var3

1234 SP500 NASDAQ Russell

Any ideas are appreciated!

Re: Combine multiple rows of character data

novinosrin — Tue, 04 Feb 2020 01:29:35 GMT


data have;
input Policy        (   Var1                 Var2                  Var3	) ($);
cards;
1234             SP500    . .  
1234               .   NASDAQ .
1234             . .       Russell
;

data want;
 update have(obs=0) have;
 by policy;
run;

proc print noobs;run;

Policy Var1 Var2 Var3

1234 SP500 NASDAQ Russell

Re: Combine multiple rows of character data

Pmyosh — Tue, 04 Feb 2020 02:42:41 GMT

Hi, if you have 1 value per variable per policy then you can do this

PROC SQL ;
  CREATE TABLE want AS
  SELECT policy
               ,MAX(Var1) AS Var1
               ,MAX(Var2) AS Var2
               ,MAX(Var3) AS Var3
  FROM have
  GROUP BY policy ;
QUIT ;

Re: Combine multiple rows of character data

Huijing — Tue, 04 Feb 2020 02:46:13 GMT

Thank you!