topic CSV data clean- convert specific rows into columns in New SAS User

CSV data clean- convert specific rows into columns

Agent1592 — Fri, 16 Nov 2018 02:01:53 GMT

Dear community, I am trying to save time. I have Excel Data cleaning work. Unfortunately the data comes all in one column but I can't transpose it simply into rows. I need to extract specific information as in the example want file. Can someone please help.

This is the have file:

Walmart Inc.

NYSE : WMT

D-U-N-S# 051957769

Berkshire Hathaway Inc.

NYSE : BRK.B

D-U-N-S# 001024314

And this is the want file:

Company Name	Exchange	Ticker	D-U-N-S#
Walmart Inc.	NYSE	WMT	51957769
Berkshire Hathaway Inc.	NYSE	BRK.B	1024314

Re: Excel (CSV) data clean- convert specific rows into columns

RW9 — Mon, 12 Nov 2018 09:31:53 GMT

CSV is not Excel!!

As for the data, if its all in that format, then read line, and retain the variables, something like:

data want;
  infile "have.csv" dlm=",";
  length company exchange ticker duns buff $200;
  retain company exchange ticker duns;
  input buff $;
  if index(buff,":") then do;
    exchange=scan(buff,1,":");
    ticker=scan(buff,2,":");
  end;
  else if substr(buff,1,4)="D-U-" then do;
    duns=scan(buff,2,"#");
    output;
    company="";
    exchange="";
    ticker="";
    duns="";
  end;
  else company=buff;
run;

Re: Excel (CSV) data clean- convert specific rows into columns

Ksharp — Mon, 12 Nov 2018 13:39:34 GMT

options validvarname=any;
data have;
infile 'c:\temp\have.csv' truncover termstr=lf;
input x $200.;
if not findc(x,':#') then group=0;
group+1;
run;
data temp;
 set have;
 if group=1 then do;n+1;name='CompanyName      ';v=x;output;end;
 else if findc(x,':') then do;
 name='Exchange';v=scan(x,1,':');output;
 name='Ticker';v=scan(x,-1,':');output;
 end;
 else if findc(x,'#') then do;
 name='D-U-N-S#';v=scan(x,-1,' ');output;
 end;
drop x group;
run;
proc transpose data=temp out=want;
by n;
var v;
id name;
run;

Re: Excel (CSV) data clean- convert specific rows into columns

Agent1592 — Tue, 13 Nov 2018 06:04:56 GMT

Thank you. I appreciate your quick response. This is an excellent solution!

Re: Excel (CSV) data clean- convert specific rows into columns

Agent1592 — Tue, 13 Nov 2018 06:05:47 GMT

Thank you sir. Excellent solution as well.