s******r
发帖数: 1524 | 1 I have a data set like
Year location ID
2007 NY a
2007 NY b
2008 CA c
2008 CA d
2008 CA e
2008 FL f
and would like to output to a txt file with format like
2007 NY a b
2008 CA FL c d e f
the location number for each year is limited but ID per year could go up to
1000+. I am thinking of using lag. However no idea what's the maximum lag
could go back.
Any idea more efficient way to handle it? Thanks, |
p********a
发帖数: 5352 | 2 data a;
input year loc $ id $;
datalines;
2007 NY a
2007 NY b
2008 CA c
2008 CA d
2008 CA e
2008 FL f
run;
proc sort data=a;
by year loc;
proc transpose data=a out=b(drop=_name_) prefix=all_id;
by year loc;
var id;
proc print;
run; |
p********a
发帖数: 5352 | 3 oops, didn't see CA and FL should be on the same line... |
p********a
发帖数: 5352 | 4 data a;
input year loc $ id $;
datalines;
2007 NY a
2007 NY b
2008 CA c
2008 CA d
2008 CA e
2008 FL f
run;
proc sort data=a;
by year;
proc transpose data=a out=b(drop=_name_) prefix=all_id;
by year;
var id;
run;
proc sql;
create table a1 as
select distinct year,loc from a;
proc transpose data=a1 out=c(drop=_name_) prefix=all_loc;
by year;
var loc;
proc sql;
create table d as
select c.*, b.* from c,b
where c.year=b.year;
proc print;
run; |
s******r
发帖数: 1524 | 5 that's my headache.
I transpose location first and then output Id one by one. Since ID could go
up to 1000+ or even more. I do not dare to transpose them all at one time.
【在 p********a 的大作中提到】
: oops, didn't see CA and FL should be on the same line...
|
l*****k
发帖数: 587 | 6 use perl... read to hashs
【在 s******r 的大作中提到】
: I have a data set like
: Year location ID
: 2007 NY a
: 2007 NY b
: 2008 CA c
: 2008 CA d
: 2008 CA e
: 2008 FL f
: and would like to output to a txt file with format like
: 2007 NY a b
|
