topic Re: Sorting Data in SAS Programming

Sorting Data

AbuChowdhury — Wed, 04 Jun 2014 10:01:25 GMT

Hi,

I attach a xls file. I need to have a dataset in SAS and it should be in panel format like below:

CompanyISIN Year Value

US9311421039 2004 284310000

US9311421039 2005 312101000

US9311421039 2006 348368000

CA123456789 2004

CA123456789 2005

How can I do that? After importing to SAS, when I am running codes in SAS, it shows the error message that all variables should be in the same format i.e. character or numeric. I also would like to change the n.a observations (in xls file) to blank observation in SAS.

Abu Chowdhury

Re: Sorting Data

RW9 — Wed, 04 Jun 2014 10:14:53 GMT

Hi,

Could I clarify:

"After importing to SAS" - How are you importing to SAS, is this working and creating a dataset, if not what warnings/errors are you getting.

"running codes in SAS" - Do you means programs that you are running based on the imported data, if so see first point, is the data there, if so then provide the code which is failing and the error/warning so we can see what is failing.

Seeing as how the code:

proc import datafile="s:\temp\rob\USA_oprev.xls" out=tmp;

run;

Works fine for me and creates a dataset either the method of importing is failing, or something in your "codes" are failing.

Re: Sorting Data

AbuChowdhury — Wed, 04 Jun 2014 10:28:02 GMT

Hi,

I can import to SAS but it does not show the year (2013, 2012 ets) in SAS dataset, Rather it shows (A, B, C) as column names.

''Running codes in sas'' -- after importing the xls file to SAS, when I was trying to sort the dataset according to the format that I mentioned before.

Actually, I need to sort the dataset according to the way I mentioned before.

Re: Sorting Data

RW9 — Wed, 04 Jun 2014 10:39:04 GMT

You have getnames set to no then for the import. This code will get you the required output (not the nicest way to normalize data (i.e. getting years from across to down), however it works).

proc import datafile="s:\temp\rob\USA_oprev.xls" out=tmp replace;

getnames=yes;

run;

data want (keep=companyisin year value);

set tmp;

attrib year format=$4.;

year="2013"; value=_013; output;

year="2012"; value=_012; output;

year="2011"; value=_011; output;

year="2010"; value=_010; output;

year="2009"; value=_009; output;

year="2008"; value=_008; output;

year="2007"; value=_007; output;

year="2006"; value=_006; output;

year="2005"; value=_005; output;

year="2044"; value=_004; output;

run;

Re: Sorting Data

AbuChowdhury — Wed, 04 Jun 2014 10:56:58 GMT

I used getnames when I imported, but still it did not show the years. It shows only the CompanyISIN as variable name, and instead of showing years (2013, 2012 .... ) it showed variable names as A, B, C .... etc.

Re: Sorting Data

RW9 — Wed, 04 Jun 2014 12:17:08 GMT

So, to check you ran the proc import code I gave above, in SAS 9.3 and you still get variable names A, B, C?

Re: Sorting Data

Ksharp — Wed, 04 Jun 2014 12:55:16 GMT

proc import datafile='c:\temp\USA_oprev.xls' out=temp dbms=excel replace;mixed=yes;getnames=no;run;
data want;
 set temp(firstobs=2);
 array x{10} _temporary_ (2013     2012     2011     2010     2009     2008     2007     2006     2005     2004);
 array y{10} _numeric_;
CompanyISIN=f1;
do i=1 to 10;
 year=x{i};
 value=y{i};
 output;
end;
keep CompanyISIN year value;
run;

Xia Keshan

Re: Sorting Data

AbuChowdhury — Wed, 04 Jun 2014 21:15:30 GMT

Hi Xia,

Your codes work fine. Now for another xls file (attached now), I would like to sort the data in the same panel format.

At first I used the following codes to import:

proc import datafile='c:\temp\USA_mv.xls' out=temp dbms=excel replace;mixed=yes;getnames=yes;run;

Then I used the following codes to sort the data in the panel format that I mentioned before:

data want;
set temp;
length code $ 40;
array a{*} an: bmg: bsp: ca: ch: gb: ie: il: kyg: lr: mhy: nl: pa: sg: us: vgg: ;
do i=1 to dim(a);
code=vname(a{i});
value=a{i};
output;
end;
keep year code value;
run;

These codes are not working. It shows the error message: ''all variables in the array list must be same type i.e. all character or numeric''.

How to solve for this xls file?

Re: Sorting Data

AbuChowdhury — Wed, 04 Jun 2014 21:29:58 GMT

Hi,

Your codes also work fine.

Re: Sorting Data

Ksharp — Thu, 05 Jun 2014 12:42:37 GMT

OK. Try this one :

proc import datafile='c:\temp\USA_mv.xls' out=temp dbms=xls replace;mixed=yes;getnames=yes;run;
data want;
 set temp;
 array x{*} us:;
 do i=1 to dim(x);
  CompanyISIN =vname(x{i});
  value=x{i};
  output; 
end;
keep year CompanyISIN value;
run; 
proc sort data=want; by CompanyISIN year;run;

Xia Keshan

Re: Sorting Data

AbuChowdhury — Sat, 07 Jun 2014 23:10:49 GMT

Hi, thanks. Your codes work fine. But still there is a problem. There are no observations in any year for some variables. When I include those variables then it shows the error message: all variables in array list must be the same character i.e. character or numeric.

Look at the complete excel file.

Re: Sorting Data

Ksharp — Sun, 08 Jun 2014 02:32:34 GMT

OK. It seems that your data is a little dirty.

 


 
proc import datafile='c:\temp\USA_mv.xls' out=temp dbms=xls replace;mixed=yes;getnames=yes;run;
data want;
 set temp;
 array x{*} _numeric_;
 array y{*} _character_;
 do i=1 to dim(x);
  CompanyISIN =vname(x{i});
  value=x{i};
 if upcase(CompanyISIN) ne 'YEAR' then output; 
end;
 do i=1 to dim(y);
  CompanyISIN =vname(y{i});
  value=input(y{i},?? best32.);
  output; 
end;
keep year CompanyISIN value;
run; 
proc sort data=want; by CompanyISIN year;run;

Xia Keshan

Re: Sorting Data

AbuChowdhury — Sun, 08 Jun 2014 15:13:01 GMT

Yes, they are. Thanks a lot Xia.