06-29-2016 07:45 AM
((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;
06-29-2016 08:22 AM - edited 06-29-2016 08:23 AM
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 ?
06-29-2016 08:20 AM
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?
06-29-2016 08:35 AM
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.
06-29-2016 08:59 AM
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
06-29-2016 09:25 AM
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.
06-29-2016 09:30 AM
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)
06-29-2016 09:44 AM
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?
06-29-2016 08:45 AM
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.
Need further help from the community? Please ask a new question.