Sorting Data

[AbuChowdhury]

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

[RW9]

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.

[AbuChowdhury]

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.

[RW9]

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;

[AbuChowdhury]

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.

[RW9]

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?

[AbuChowdhury]

Hi,

Your codes also work fine.

[Ksharp]

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

[AbuChowdhury]

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?

[Ksharp]

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

[AbuChowdhury]

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.

[Ksharp · Accepted solution]

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

[AbuChowdhury]

Yes, they are. Thanks a lot Xia.