Error in proc report group level and report level aggregation

[axel_p]

data WORK.REPORT1;
   infile datalines dsd truncover;
   input Category:$5. Level:$7. Sub_Level:$9. Description:$7. Weightage:15.2 Unweighted_Amount:BEST. Weighted_amount:$1.;
   format Weightage 15.2 Unweighted_Amount BEST.;
   label Category="Category" Level="Level" Sub_Level="Sub_Level" Description="Description" Weightage="Weightage" Unweighted_Amount="Unweighted_Amount" Weighted_amount="Weighted_amount";
 datalines;
 Asset       . .  
 Asset Level 1     . .  
 Asset Level 1 Level 1 A   . .  
 Asset Level 1 Level 1 A Sample1 1.00 2779  
 Asset Level 1 Level 1 A Sample2 0.40 1356  
 Asset Level 1 Level 1 A Sample3 0.80 2424  
 Asset Level 1 Level 1 A Sample4 0.20 2992  
 Asset Level 2     . .  
 Asset Level2 Level 2 A   . .  
 Asset Level2 Level 2 A Sample1 0.90 1371  
 Asset Level2 Level 2 A Sample2 0.75 2465  
 Asset Level2 Level 2 A Sample3 0.45 2648  
 Asset Level2 Level 2 A Sample4 1.00 1529  
 Asset Level2 Level 2 B   . .  
 Asset Level2 Level 2 B Sample1 0.50 1208  
 Asset Level2 Level 2 B Sample2 0.50 1351  
 ;;;;

I have a proc report requirement in which I need to aggregate the "Weighted_amount" column for the group " sub_level" and then for the overall report as descried  below 

Weighted amount is a variable I need to compute , Weighted_amount is a product of weightage and Unweighted_amount . 

I first need to compute  the aggregate of the individual sub_level rows  before each sub_level eg Level 1A , Level 2A , Level 2B  at break / summarize at line 4, 10 , 15 respectively  , then compute the Total "Level" amount by  aggregating individual sub-level totals and break / summarize at line 3 & 9 and finally compute the Total asset at the top of the report as 

Total Asset= Level 1 Assets+ 0.5*Level 2 Assets

The error I'm getting is ERROR: Weighted_amount is an ANALYSIS variable but not numeric.

Code I have tried so far

data Report1a;
set Report1;

if Weightage > 0 then 
Weighted_amount = Weightage*Unweighted_Amount;
run;

proc report data= Report1a nowd 
			out=Report1b;
column Category Description Level Sub_Level Unweighted_Amount Weightage Weighted_amount;
define Category / display;
define Description / display;
define Level / group ;
define Sub_Level / group;
define Level / group ;
define Unweighted_Amount / display;
define Weightage / display;
define Weighted_Amount / analysis ;

	
compute before Sub_Level; 
 total = Weighted_Amount.sum; 
 endcomp;
 
 
break before Sub_Level / summarize;
rbreak before / summarize;
run;

Dataset attached as a text file 

Look forward to your expert help 

[Kurt_Bremser]

Your datalines code does not create the dataset as intended, please review that.

[axel_p]

I have used both the methods described in this post to replicate the dataset for your perusal https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
this has created the datalines code which I have copy pasted here as well as a text file which I have attached in my opening post  .. Please have a look again

edit  : @Kurt_Bremser  is there are any other way of replicating the dataset if the methods in the above link are not fulfilling the purpose 

[Kurt_Bremser]

Your datalines have at most 9 columns (all separated by blanks), your INPUT statement only 7. This does not look right to me.

[ballardw]

Your input statement shows :

 Weighted_amount:$1.;

Which reads the variable as character.

So this:

"The error I'm getting is ERROR: Weighted_amount is an ANALYSIS variable but not numeric."

would be expected. So either your data step is incorrect or you have misunderstood what an Analysis variable is.

Suggest you go back and make sure the when you created or read your Weighted_amount variable that it is in fact numeric.

I suspect that your LOG shows comments about conversion of character to numeric, in this step:

data Report1a;
set Report1;

if Weightage > 0 then 
Weighted_amount = Weightage*Unweighted_Amount;
run;

Since the Report1 data step defined Weighted_amount as character the arithmetic would be performed but the value written into Weighted_Amount would still be character (and only one character long to boot).

[axel_p]

@ballardw thank you for your reply 

 Weighted_amount:$1.;

the above line of code is for the formatting of the variable for replicating the dataset  and not what I'm using in my script as part of the data step for computing and describing the variable "Weighted_amount"

So if I do not define the type of variable in the data step , will it default to character  ?

moreover after adding a line of code defining the format for Weighted_amount as a dollar numeric 

data Report1a;
set Report1;

if Weightage > 0 then 
Weighted_amount = Weightage*Unweighted_Amount ;
format Weighted_amount dollar7.2 ;
run;

I get another error in addition to the earlier one :- 

ERROR: Weighted_amount is an ANALYSIS variable but not numeric.

ERROR: Weighted_amount must use a character format.

I tried changing the format to just numeric  

data Report1a;
set Report1;

if Weightage > 0 then 
Weighted_amount = Weightage*Unweighted_Amount ;
format Weighted_amount 7.2 ;
run;

but again it yields the same errors 

could you please you specify the exact line of code for delineating "Weighted_amount" as a numeric variable and the location ? 

Regards

[andreas_lds]

Maxim 3

Know your data.

Having a clear picture of data structures – variable types, lengths, formats – and content will provide you with a fast-path to solving problems. Many simple problems can be cleared by taking a look at the "Columns" section in dataset properties. Use proc contents frequently.

You must know whether "Weighted_amount" is a numeric or alphanumeric variable. Run proc contents on the dataset an read the results.

If it is a alphanumeric variable, it is impossible to use it as analysis variable, because you can't use those variables in calculations.

[ballardw]

 Weighted_amount:$1.;

the above line of code is for the formatting of the variable for replicating the dataset  and not what I'm using in my script as part of the data step for computing and describing the variable "Weighted_amount"

If that is not what you used then why use that to provide that as example data???

That does not format. That is an instruction on how to read the data. It means "read the value into a character length one value". It makes the variable character.

So if I do not define the type of variable in the data step , will it default to character ?

SAS will default to NUMERIC with a BEST. format for values unless you provide instructions otherwise.

moreover after adding a line of code defining the format for Weighted_amount as a dollar numeric

That is done with a FORMAT statement, not INFORMAT or INPUT, which involve reading data. The Format you want is named DOLLARw.d such as Dollar16.2 to display upto 16 characters including a $ commas and decimal point with two decimal values.

data Report1a;
set Report1;

if Weightage > 0 then 
Weighted_amount = Weightage*Unweighted_Amount ;
format Weighted_amount 7.2 ;
run;

but again it yields the same errors 

How about the ERRORS associated with that data step? There would be an error similar to this. First code to create a small data set the way that you showed:

data junk;
   input x :$1.;
datalines;
12345
;

data junk2;
  set junk;
  format x 7.2;
run;

And here is what the LOG of the second data step shows:

622  data junk2;
623    set junk;
624    format x 7.2;
                ---
                230
ERROR 230-185: Invalid character format/informat decimal specified.

625  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.JUNK2 may be incomplete.  When this step was stopped there were 0
         observations and 1 variables.

If you already have a data set this error means the previous data set was not replaced an you did nothing to change the property.

If a variable is character you cannot assign a numeric format to it. Period. You also cannot assign character formats to numeric values. Formats are for display an much match the variable type.

Unfortunately many people will use "format" from other languages in a different manner than SAS does and that can cause some confusion. Formats in SAS are in effect named display rules. I can have a custom format for a numeric values that would display a 1 (numeric one) as text "This is a one", or "Coded value for response choice: Eat Fish Daily" .

could you please you specify the exact line of code for delineating "Weighted_amount" as a numeric variable and the location ?

I don't think so. Your first data step has so many problems I don't what you expect. As others point out you are reading more values than are on your input lines and when I attempt your code the only thing read correctly (maybe) is  Asset. If you are not going to READ your Unweighted_amount it does not belong on an INPUT statement. That is what INPUT does, reads data.

If this reads the shown line of data correctly, as it look at the data set or print, maybe:

data WORK.REPORT1;
   infile datalines dsd truncover dlm=',';
   input Category:$5. Level:$7. Sub_Level:$9. Description:$7. Weightage Unweighted_Amount;
   format Weightage 15.2 ;
   if Weightage > 0 then 
        Weighted_amount = Weightage*Unweighted_Amount;
 datalines;
 Asset,Level 1,Level 1 A,Sample1,1.00,2779  
 ;;;;

I took out your label statement as it was duplicating the default label of the variable. Notice that I made some guesses as to where the character values end and used the comma as a delimiter between them with that indicated on the INFILE statement.

Reading spaces as part of the value is a somewhat tricky bit. Basically if you are going to use list input (the form of input statement  you used) then delimiting the values is best. Otherwise you must make sure that the values start and end in specific columns for each variable and use one of the approaches to reading fixed column input, either column spedifications or fixed formats. But your datalines did not do that.

It is a good idea to place simple calculations and such in the same step that reads the data. Then you don't get into multiple data sets and getting confused about what comes from where.