topic Re: transforming SAS data in SAS Programming

transforming SAS data

buddha_d — Thu, 20 Jul 2017 10:35:05 GMT

I am reading a tab delimited file with infile statement and input. I need to do tranformation and concatenate couple of variables and export it to tab delimited file. But the output has more spaces than the original. Has anyone experienced this?

Thanks,

Re: transforming SAS data

PeterClemmensen — Thu, 20 Jul 2017 10:41:27 GMT

Can you provide some sample data?

Re: transforming SAS data

Kurt_Bremser — Thu, 20 Jul 2017 11:06:51 GMT

Post some lines of the input file that illustrate the effect, and the code you used. Use the proper posting method ({i}) to preserve formatting.

Re: transforming SAS data

buddha_d — Thu, 20 Jul 2017 11:08:46 GMT

I am adding _sequence number to station field (based on date ) like 1/26/2009 date will have B-123_1 & 3/23/2009 will have B-123_2.

once I have it like this transformation of data, I need to export data. I have very large data but submitting small set.

Thanks

Re: transforming SAS data

Kurt_Bremser — Thu, 20 Jul 2017 11:35:45 GMT

Your data is defective after record 10 (missing tab character after station_description), so I limited my code to the first 10 lines:

data have;
infile '$HOME/sascommunity/EColi_tab.txt' dlm='09'x firstobs=2 obs=10 truncover;
input
  Record
  STATION :$6.
  STATION_DESCRIPTION :$20.
  COLLECTION_DATE :mmddyy10.
  FecalColi
  FecalRemark :$5.
  Enterococci
  EnteroRemark :$5.
  Ecoli
  EcoliRemark :$5.
  Nav_Status :$6.
;
format COLLECTION_DATE mmddyy10.;
run;

proc sort data=have;
by station collection_date;
run;

data want;
set have;
by station;
if first.station
then counter = 1;
else counter + 1;
station_seq = trim(station) !! '_' !! strip(put(counter,best.));
run;

proc export data=want outfile='$HOME/sascommunity/want.txt' dbms=dlm;
delimiter='09'x;
run;

The resulting text file is attached.

Re: transforming SAS data

buddha_d — Thu, 20 Jul 2017 12:16:33 GMT

Thanks for prompt reply. I want to import the exported file to match the old one with little code difference, but I wouldn't be able to do it. both data want and have work alright.

Thanks again