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

hey guys, this is my code below:

DEFAULT is a numeric variable. Risk_grade is a character variable.

This code involves transposing from rows to columns.

The do loop 'j' is used to look through the variable list in the dataset i.e. RISK_GRADE DEFAULT.

Variable "period" is more a counter i.e. _N_

If variables RISK_GRADE and DEFAULT were both numeric this code works perfectly. However, since RISK_GRADE is character and DEFAULT is numeric there is a problem. This also does not work if variables RISK_GRADE and DEFAULT are both character format.

ERROR: Variable RISK_GRADE has been defined as both character and numeric.

Below is my code:

     

data workingmodel3NUMtmp ;
            array Array_list[2,114] $2. RISK_GRADE_A1-RISK_GRADE_A114 DEFAULT_A1-DEFAULT_A114;
            array var_list[2] RISK_GRADE DEFAULT;
            retain RISK_GRADE_A1-RISK_GRADE_A114 DEFAULT_A1-DEFAULT_A114 ;
            KEEP CLIENT_NO SEGMENT_LOCAL SEGMENT_PILLAR_III RISK_GRADE_A1-RISK_GRADE_A114 DEFAULT_A1-DEFAULT_A114;
            set work.tmp1;
            by CLIENT_NO;
    
            if first.CLIENT_NO then
                        i = 0;
            i + 1;
    
            do j=1 to 2;
                        Array_list[j,period]=var_list[j];
            end;
    
            if last.CLIENT_NO then
                        output;
    run;

 

 

 

All help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I would imagine that its because on this row:

	array Array_list[1,10] DEF_A1-DEF_A10; /* should also contain variables in character format */
	array var_list[1] DEF;

No format is specified, so it defaults to numeric:

array array_list[1,10] $ REG_A1-REG_A10.

 

Again though, there are more data elements that supplied.  If you can give an example of what your output should look like as this doesn't seem to be the best way to do things, why not just tranpose reg, then transpose def, and merge the two datasets back together?

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

So what is the question precisely?  The error is quite clear an array can only be all of one type of data, either numeric of character.  Why has your data changed would be my first question.  Second question would be why are you using wide (transposed) datasets, when a long (normalised) data strcuture would probably be far easier to work with, and wouldn't allow you to put character data into a numeric variable.  Also, for specific help, its a good idea to post test data (couple of observations) which reflect your data, in the form of a datastep and what the output should look like.

sebster24
Quartz | Level 8

Hello RW9,

Thank you for your quick response.

My query is that I am trying to transpose the data using arrays, as proc transpose does not provide me the same flexibility. My data contains both character and numeric formats. For the code I have written (framed below with dataset), the tranpose procedure works successfully for NUMERIC format variables, but fails to work for CHARACTER format variables.

The tranpose operation is required to match existing formats which are currently compiled in excel - (I Know - a big mess 🙂 ) which is then used for reporting purposes.

 

data work.tmp1;
input  client $2. RIG $3. DEF period;
datalines;
57 A2 0 1
57 B1 0 5
57 B1 0 6
57 A2 0 7
57 A2 0 8
57 A2 0 9
57 A2 0 10
58 B2 0 5
58 B2 0 6
58 C2 0 7
58 C1 0 8
58 C1 0 9
58 C1 0 10
58 C1 0 1
58 C1 0 2
58 C1 0 3
58 B2 0 4
58 B2 0 5
58 B1 0 6
58 B1 0 7
58 B1 0 8
58 B1 0 9
58 B1 0 10
;
run;





data ONLY_NUM_FORMAT;
/*  this works */
	array Array_list[1,10] DEF_A1-DEF_A10;
	array var_list[1] DEF;
	retain DEF_A1-DEF_A10 ;
	KEEP CLIENT DEF_A1-DEF_A10;
	set work.tmp1;
	by CLIENT;

	if first.CLIENT then
		i = 0;
	i + 1;

	do j=1 to 1;
		Array_list[j,period]=var_list[j];
	end;

	if last.CLIENT then
		output;
run;

data ONLY_CHAR_FORMAT;
/* THIS DOES NOT WORK */
array Array_list[1,10] RIG_A1-RIG_A10;
	array var_list[1]  RIG;
	retain RIG_A1-RIG_A10 ;
	KEEP CLIENT RIG_A1-RIG_A10;
	set work.tmp1;
	by CLIENT;

	if first.CLIENT then
		i = 0;
	i + 1;

	do j=1 to 1;
		Array_list[j,period]=var_list[j];
	end;

	if last.CLIENT then
		output;
run;

data I_WANT_BOTH_FORMATS;
/*  THIS DOES NOT WORK BECAUSE IT CONTAINS CHARACTER FORMAT VARIABLES */
	array Array_list[2,10] DEF_A1-DEF_A10 RIG_A1-RIG_A10;
	array var_list[2] DEF RIG;
	retain DEF_A1-DEF_A10 RIG_A1-RIG_A10;
	KEEP CLIENT DEF_A1-DEF_A10 RIG_A1-RIG_A10;
	set work.tmp1;
	by CLIENT;

	if first.CLIENT then
		i = 0;
	i + 1;

	do j=1 to 2;
		Array_list[j,period]=var_list[j];
	end;

	if last.CLIENT then
		output;
run;

 

Once again, thank you for your help 🙂

best wishes, 

S

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Not sure why you have three steps?  What is the output supposed to look like.  I have guessed a bit from the working step. Just have three arrays, and make sure they don't match existing variable.  Also note  , you have 16 elements in the second client group, so an array of 10 elements wont hold it all.  Try this: 

 

data want (drop=i def rig period);
  set tmp1;
  by client;
  array def_a{16} 8.;
  array rig_a{16} $3.;
  array period_a{16} 8.;
  retain def_a: rig_a: period_a: i;
  if first.client then i=1;
  else i=i+1;
  def_a{i}=def;
  rig_a{i}=rig;
  period_a{i}=period;
  if last.client then output;
run; 
sebster24
Quartz | Level 8

The output what you mentioned is similar to what i am after, except I am trying to automate the variable definitions instead of inputting the information manually. Hence, I am using multidimensional arrays under the first line and then using the Do loop to loop through the different variables. There are many variables, hence the need for automation. This code works perfectly for multiple NUMERIC format variables.

However, it does not work for character variables.

For example, If you replace the DEF variables with RIG in the data step code below and then run the code, it produces an error noting:

Variable RIG has been defined as both character and numeric.I dont understand why this error occurs?

It works without any issue when the values are inputted manually.

If the same code which you provided can be automated using a loop, then it would be perfect.

Alternatively, I would have to use a PROC SQL statement to generate the statements.

 

data work.tmp1;
input  client $2. RIG $3. DEF period;
datalines;
57 A2 0 1
57 B1 0 5
57 B1 0 6
57 A2 0 7
57 A2 0 8
57 A2 0 9
57 A2 0 10
58 B2 0 5
58 B2 0 6
58 C2 0 7
58 C1 0 8
58 C1 0 9
58 C1 0 10
59 C1 0 1
59 C1 0 2
59 C1 0 3
59 B2 0 4
59 B2 0 5
59 B1 0 6
59 B1 0 7
59 B1 0 8
59 B1 0 9
59 B1 0 10
;
run;





data ONLY_NUM_FORMAT;
/*  this works */
	array Array_list[1,10] DEF_A1-DEF_A10; /* should also contain variables in character format */
	array var_list[1] DEF; 
	retain DEF_A: ;
	KEEP CLIENT DEF_A:;
	set work.tmp1;
	by CLIENT;

	if first.CLIENT then
		i = 0;
	i + 1;

	do j=1 to 1;
		Array_list[j,period]=var_list[j]; /* Looks at the variables specified in var_list and then loops */
	end;

	if last.CLIENT then
		output;
run;

 

 

Thank you for being so patient with me.

regards,

S

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I would imagine that its because on this row:

	array Array_list[1,10] DEF_A1-DEF_A10; /* should also contain variables in character format */
	array var_list[1] DEF;

No format is specified, so it defaults to numeric:

array array_list[1,10] $ REG_A1-REG_A10.

 

Again though, there are more data elements that supplied.  If you can give an example of what your output should look like as this doesn't seem to be the best way to do things, why not just tranpose reg, then transpose def, and merge the two datasets back together?

Tom
Super User Tom
Super User

So you want to convert your vertical structure to a horizontal one?

There is an elegant solution using PROC SUMMARY that has been posted and presented a SUGI.  Here is a link to one such posting.

 

http://www.sascommunity.org/wiki/Transpose_using_IDGROUP_in_PROC_SUMMARY

 

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 1766 views
  • 1 like
  • 3 in conversation