数据中有两个字的城市名,不能作变量名,所以用了label来显示。
最后一段程序用到了array。
- PROC IMPORT OUT= WORK.original
- DATAFILE= "C:\Temp\Original_Data.xls"
- DBMS=EXCEL REPLACE;
- SHEET="original_data$";
- GETNAMES=YES;
- MIXED=NO;
- SCANTEXT=YES;
- USEDATE=YES;
- SCANTIME=YES;
- RUN;
- proc sql;
- create table texas as
- select distinct zip, state, primary_city, county
- from original
- where state = "TX"
- order by primary_city, zip, county;
- quit;
- data texas_small; /* create a small table for testing */
- set texas (obs=20);
- run;
- proc sql noprint;
- select count(distinct Primary_city) into: n_city from texas_small;
- select max(a.n_zip) into : max_nzip
- from (select count(zip) as n_zip
- from texas_small
- group by primary_city) as a;
- quit;
- proc sql;
- create table primary_city as
- select distinct primary_city as city from texas_small order by primary_city;
- select cats("city", monotonic(),"=", "'", city, "'") into: label_city
- separated by ' '
- from primary_city;
- quit;
- data want;
- array city {&n_city} $20. ;
- array temp{&n_city, &max_nzip};
- set texas_small end=last;
- by primary_city;
- label &label_city;
- retain i_city 0 temp:;
- if first.primary_city then do;
- i_city + 1 ; i_zip=1;
- end;
- temp[i_city, i_zip]= zip;
- i_zip + 1;
- if last then do;
- do k1 = 1 to &max_nzip;
- do k2 = 1 to &n_city;
- city[k2] = temp[k2,k1];
- end;
- output;
- end;
- end;
- drop i_city i_zip k1 k2 temp: zip state primary_city county;
- run;