## why one part is not working

Solved
Occasional Contributor
Posts: 13

# why one part is not working

((I am making a macro to try to add extra number between 0 to 9 at the end of of certain variabel to make a balance, it's work to get the new variable origtillg to spare the new value, but it's not putting the new value (CATS(i,j) to the previous variabel (i) and not making the correct tabel, what i am missing?, big thanks in advance))

IF SUM(V7200,V7210,V7211,V7212,V7213,V7240,V7250,V7260,V7280) < SUM(V7300,V7320,V7330,V7380,V7381,V7382,V7383) then do;

Do i= V7200,V7210,V7211,V7212,V7213,V7240,V7250,V7260,V7280;

Do j=0 to 9;

if SUM(V7200,V7210,V7211,V7212,V7213,V7240,V7250,V7260,V7280,(-1)*i,CATS(i,j))=SUM(V7300,V7320,V7330,V7380,V7381,V7382,V7383) and i ne 0 then do;

origtillg=CATS(i,j) ;

i=origtillg;

LABEL origtillg=CATS('orig',i);

END;

END;

END;

END;

Accepted Solutions
Solution
‎06-29-2016 10:38 AM
Occasional Contributor
Posts: 13

## Re: why one part is not working

It's only an assumption that the user has forgot one number.

Yes you are right, I will create a new variable that act as a correctin factor

Thanks all for your help

All Replies
Super Contributor
Posts: 340

## Re: why one part is not working

Posted in reply to sylvainbg
Hello,
You are modifying the value of i inside the do loop on the different values of i.
Occasional Contributor
Posts: 13

## Re: why one part is not working

Thanks for the info.

How should I do, if I want to modify the value of i?  (I want to change the value for the i that is getting save on origtill). What about the labeL?

Super Contributor
Posts: 340

## Re: why one part is not working

[ Edited ]
Posted in reply to sylvainbg

I don't understand exactly what You are trying to do. But when the interpreter meets the end of the "do i" loop, i will be reset to the next value of the list V7200,V7210,V7211,V7212,V7213,V7240,V7250,V7260,V7280. So the only use of the new i value You computed is to determine the label. In that case, You can use another variable than i or directly origtillg : LABEL origtillg=CATS('orig',origtillg);

Why do You have to modify the looping variable i and what effect do You expect ?

Super User
Posts: 23,776

## Re: why one part is not working

Posted in reply to sylvainbg

Unfortunately I don't think the label can be dynamically generated.

I cant follow what your trying to do in your code. Can you explain it and possibly provide a sample input and expected output dataset?

Also, is there a reason you aren't using an array declaration here but have listed all your variables every time?

Super User
Posts: 5,884

## Re: why one part is not working

By a quick look, it seems that you have lot of columns.

Calculations are typically simpler when you data stored in normalized, long format (rather than wide).

Elaborate about the use case and desired outcome.

Data never sleeps
Occasional Contributor
Posts: 13

## Re: why one part is not working

I am still a new user of SAS.

Thanks all for your help, let me explain more and give an example.

The database is an extract of results and balance sheets from compagnies of a country (database is around 1 million lines and 150 variables.

V7200 ...V7280 are part of the assets (right side of the balance sheet)

V7300....V7383 are part of the debts (left side of the balance sheet)

It's a rule that assets needs to be equal to debts, users have been sometimes forgetting to write a number at the end of one of the variable. The objective is to add the extra number in order to recover assets = debts.

For example let's take

V7200=0, V7210=456, V7211=2874, others are 0 (for this exemple)

V7300=210. V7320=28746,V7380=246, others are 0 (for this exemple)

The normal value for V7211 should had been 28746.

I would like the macro to test for each of the variabel (V7200 ...V7280) when assets are < debts and the variabel is not 0 to add an extra number. If with the extra number we get assets = debts then a new variabel is created and saving the previous value (on the exempel 2874, putting the new value 28746 back to V7211 and putting as name for the created variabel origtillg_V7211 on this exempel.

I hope I was clear

Super User
Posts: 23,776

## Re: why one part is not working

Posted in reply to sylvainbg

How do you know which value needs to be modified in that scenario? Since the sum is what matters couldn't any of the variables be modified and it would be 'correct'?

Occasional Contributor
Posts: 13

## Re: why one part is not working

Yes it's a risk to create a wrong value but as it's only doing when assets are different than debts. It's better than not making a change. Values are usually very long.

Super User
Posts: 10,280

## Re: why one part is not working

Posted in reply to sylvainbg

How do you determine which variable is faulty?

Without such a rule, everything you do is futile and will only introduce more errors (and possibly hidden ones at that).

You can only detect faulty observations and throw them back to the accountants.

Since your data looks like accounting data, I question why you need manually entered numbers at all?

Have them give you a proper export of their data for importing into the SAS system.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 13

## Re: why one part is not working

Posted in reply to KurtBremser

Thanks for the information. I will keep on the part to identify faulty information as can't do better.

I am doing an internship in an official agency that gets information from an other official agency (so not possible to contact the accountats that did wrong)

Super User
Posts: 23,776

## Re: why one part is not working

Posted in reply to sylvainbg

My suggestion would be to not modify the original variables or even try to change them. Simply create another variable that will act as a correction factor and zeroes the debts and liabilities.

By the way, how do you know if it's the assets or liabilities that are incorrect?

Solution
‎06-29-2016 10:38 AM
Occasional Contributor
Posts: 13

## Re: why one part is not working

It's only an assumption that the user has forgot one number.

Yes you are right, I will create a new variable that act as a correctin factor

Thanks all for your help

Super User
Posts: 10,280

## Re: why one part is not working

Posted in reply to sylvainbg

Using the label statement in this way is nonsense.

The label is a static attribute for a column set at data step compile time.

Therefore you end up with the column origtillg having the label "CATS(orig,i)", which will be displayed as a column header when using proc print or similar with the label option. Note that SAS discards the single quotes when converting your text to the label string.

A label cannot be changed at data step execution time.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
🔒 This topic is solved and locked.

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

Discussion stats
• 13 replies
• 762 views
• 0 likes
• 5 in conversation