BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AbuChowdhury
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

Hi,

Your codes also work fine.

Ksharp
Super User
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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

Yes, they are. Thanks a lot Xia.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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