Convert one table into another format from SAS code

Question

I have a table is SAS which looks like this.

year    Country Host Code Value
2010    India   Pak 220 111
2010    India   Aus 220 123
2010    India   NZ  220 23
2010    India   SA  240 43
2010    India   WI  250 124
2010    India   SRI 250 325
2010    India   ZIM 280 235

i want to transform this table to following form

Country Code    Pak_2010    Aus_2010    NZ_2010 SA_2010 WI_2010 SRI_2010 IM_2010
India   220      111       123             23    0          0       0       0
India   240       0         0              0     43         0       0       0
India   250       0         0              0     0        124       325     0
India   280       0         0              0     0          0       0       235

for one country and code, there will be one value.

Can anyone please suggest me code for doing this transformation?

Show the code you've already tried, SO is not a code-on-demand service. — Chris J, Jan 25 '15 at 14:39
possible duplicate of [Is there an efficient way of transposing huge table in SAS](http://stackoverflow.com/questions/16809879/is-there-an-efficient-way-of-transposing-huge-table-in-sas) — user667489, Jan 25 '15 at 15:18
Suggested Google Search: UCLA transpose long to wide using data step — Reeza, Jan 25 '15 at 16:19

Dirk Horsten · Answer 1 · 2015-01-26T14:25:10.753

0

This is a classical proc transpose, separating your ID variables with a delimiter:

PROC TRANSPOSE 
    DATA=yourInput
    OUT=yourOutput(drop=_name_)
    DELIMITER=_;
BY Country Code;
ID Host Year;
VAR Value;

edited Jan 26 '15 at 14:25

answered Jan 25 '15 at 17:55

Dirk Horsten

3,753
4
20
37

Some error is coming as.. The ID value " ' 4423204'n" occurs twice in the same BY group. – Sunil Sharma Jan 26 '15 at 13:53
same error.....It has to be Host and Code but i want year on the column side. For example..For code 220 and Country India..i want Pak_2010 and Aus_2010 as columns..which should have value of 111 and 123 respectively. – Sunil Sharma Jan 26 '15 at 14:04
Got the chance to run it myself. Removed the quotes around the delimiter. – Dirk Horsten Jan 26 '15 at 14:26
for this data set..it is working...but for a large data set...where i have 'code' variable same for many countries...but with different commodity code. i am getting error..."The ID value xxx occurs twice in the same group by." although it should not happen...as we are grouping by country and code.... – Sunil Sharma Jan 26 '15 at 20:16
I don't think the problem is related to size. – Dirk Horsten Jan 26 '15 at 21:32

Convert one table into another format from SAS code

1 Answers1