Gather historical data

[WilliamB]

Hello,

I would like to gather historical data (T3 and T4) in proc sql and get this:

id	Pays	Price T3			id	Pays	Price T3
44	France	3			45	Allemagne	21
45	Allemagne	5			44	France	45
46	Belgique	6			46	Belgique	54
47	Espagne	7			47	Espagne	67
					48	Russie	34

Final:

id	Pays	Price T3	Price T4
44	France	3	45
45	Allemagne	5	21
46	Belgique	6	6
47	Espagne	7	7
48	Russie		34

Thank you

[gamotte]

Hello,

data pQ3;
    input id Country $  PriceQ3;
    cards;
44 France 3
45 Allemagne 5
46 Belgique 6
47 Espagne 7
;
run;

data pQ4;
    input id Country $ PriceQ4;
    cards;
44 France 45
45 Allemagne 21
46 Belgique 6
47 Espagne 7
48 Russie 34
;
run;

proc sql;
    CREATE TABLE P_hist AS
    SELECT COALESCE(a.id,b.id) AS id, COALESCE(a.Country, b.Country) AS country, a.PriceQ3, b.PriceQ4
    FROM pQ3 a
    FULL JOIN pQ4 b
    ON a.id=b.id;
quit;

Edit : added names for the calculated columns (id, Country)

[andreas_lds]

Can't see the logic filling variable T4, please explain it. Why do you want to solve the problem with proc sql?

[PeterClemmensen]

Why do you want to do this with PROC SQL?

[kiranv_]

it is much easier and cleaner to do in data step using update. 

data want;
update pQ3 PQ4;
by id;
run;

[Kurt_Bremser]

For processing, a long dataset format is to be preferred (Maxim 19). You can always transpose to wide if you need it for display.

data T3;
input id :$2. country :$10. price_t3;
cards;
44 France 3
45 Allemagne 5
46 Belgique 6
47 Espagne 7
;
run;

data T4;
input id :$2. country :$10. price_t4;
cards;
44 France 45
45 Allemagne 21
46 Belgique 6
47 Espagne 7
48 Russie 34
;
run;

%macro gather(begin,end);
data cntlin;
set
%do t = &begin. %to &end.;
  t&t. (keep=id country rename=(id=start country=label))
%end;
;
fmtname = 'mycountry';
type = 'C';
run;

proc sort data=cntlin nodupkey;
by start;
run;

proc format cntlin=cntlin;
run;

data final (keep=id period price);
set
%do t = &begin. %to &end.;
  t&t. (rename=(price_t&t.=price))
%end;
  indsname=inds
;
period = input(substr(scan(inds,2,'.'),2),best.);
format id $mycountry.;
run;
%mend;
%gather(3,4);


proc sort data=final;
by id;
run;

proc transpose
  data=final
  out=final_wide (drop=_name_)
  prefix=price_t
;
by id;
var price;
id period;
run;

proc print data=final_wide noobs;
run;

Result:

id           price_t3    price_t4

France           3          45   
Allemagne        5          21   
Belgique         6           6   
Espagne          7           7   
Russie           .          34   

Note how example data is presented in a data step with datalines.

Also note how redundant information is kept out of the dataset by using a format.