Sorting Data

Accepted Solution Solved
Reply
Regular Contributor
Posts: 183
Accepted Solution

Sorting Data

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


Accepted Solutions
Solution
‎06-07-2014 10:32 PM
Super User
Posts: 9,682

Re: Sorting Data

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,405

Re: Sorting Data

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.

Regular Contributor
Posts: 183

Re: Sorting Data

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.

Super User
Super User
Posts: 7,405

Re: Sorting Data

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;

Regular Contributor
Posts: 183

Re: Sorting Data

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.

Super User
Super User
Posts: 7,405

Re: Sorting Data

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?

Regular Contributor
Posts: 183

Re: Sorting Data

Hi,

Your codes also work fine.

Super User
Posts: 9,682

Re: Sorting Data

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

Regular Contributor
Posts: 183

Re: Sorting Data

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?

Super User
Posts: 9,682

Re: Sorting Data

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

Regular Contributor
Posts: 183

Re: Sorting Data

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.

Solution
‎06-07-2014 10:32 PM
Super User
Posts: 9,682

Re: Sorting Data

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

Regular Contributor
Posts: 183

Re: Sorting Data

Yes, they are. Thanks a lot Xia.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 1059 views
  • 6 likes
  • 3 in conversation