Changing a two-dimensional-table to one-dimension

[jimmychoi]

Hi,

my data is:

Country Code	1930	1931	…	2018	2019
AND	1	2		3	4
ATG	5	6		7	8
ABW	9	10		11	12
AUS	13	14		15	16

Desired output:

Country Code	Value
AND-1930	1
AND-1931	2
AND-…	…
AND-2018	3
AND-2019	4
ATG-1930	5
ATG-1931	6
ATG-…	…
ATG-2018	7
ATG-2019	8
…	…

Since this concatenates the names of rows and columns and reduces the dimension of the table,

I honestly don't know how to achieve it with functions such as transpose.

and for the original data, there are about 100 rows (100 different country codes) and 90 columns (from year 1930 - 2019)

Please help.

[Shmuel]

It is not clear - is the 1st row part of your data? or is it the labels row?

It seems that your input is an excel table. Have you imported it to sas?

Are years successive from 1930 up to 2019 ?

Assuming your data is already a sas data set, please post your data in form of:

data test;
   input <variables>
datalines;

... your data ...

run;

[jimmychoi]

Hi Shmuel,
yes the first row is for the labels. and the years are successive from 1930 up to 2019.
For that reason, I couldn't write the data in datalines statement.

[Shmuel]

Adapt next code to your data:

data test;
   input country $3. value1-value5;
datalines;
AND 1 2 3 4 5
ATG 6 7 8 9 10
;
run;

data want;
     length coutry_code $8 ;
  set test;
    array year y1-y5;
	array vx valu1-value5;
	if _N_ = 1 then 
       do i=1 to 5;
	      yx(i) = i +1929;
	   end;
	do i=1 to 5;
	   country_code = cat('-',country,yx(i));
	   value = vx(i);
	end;
	keep country_code value;
run;

[VDD]

use proc transpose in reverse order

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

[jimmychoi]

Trying to read and understand the link...

[VDD]

here is a sample that does basically the samething.

data Abun_dw20d_summary ;       informat bins $30.; format bins $30.; length bins $30.;
   input bins $ CM1DW20D CM3DW20D CW1DW20D ODWD W1DW4D;
   datalines;         
Acinetobacter 2477.123456789 195  163 45 77
Actinoalloteichus 2431 220  198 69.123456789 6774
Bacillus 2456 173  155 78 785
Bletilla 2412 135  116.123456789 45 123
;   
run;


proc transpose data=Abun_dw20d_summary out=bint;
    var _all_;
    by bins;
run;

[VDD]

data have;
input Country_Code $ y1930 y1931 y2018 y2019;
cards;
AND
1
2
 
3
4
ATG
5
6
 
7
8
ABW
9
10
 
11
12
AUS
13
14
 
15
16
;
proc sort data=have;
	by country_code;
run;
proc transpose data=have out=want;
    var _all_;
    by country_code;
run;

[Kurt_Bremser]

If you really have these column names, proc transpose is the way to go:

proc transpose data=have out=trans;
by country_code;
var _numeric_;
run;

And then create the final dataset in an additional data step:

data want;
set trans (
  rename=(col1=value country_code=country);
);
length country_code $8;
country_code = catx('-',country,_name_);
drop country _name_;
run;

[Astounding]

Unless you have some special need that you haven't revealed, you would be much better off keeping the YEAR separate from the COUNTRY. Several posters have shown how to do that using PROC TRANSPOSE.

It's to your advantage to learn how to program with data in that form. Here's just one example where three variables make the task easy but two variables make the task difficult: find the average VALUE for each year.