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

 

((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;

1 ACCEPTED SOLUTION

Accepted Solutions
sylvainbg
Obsidian | Level 7

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

View solution in original post

13 REPLIES 13
gamotte
Rhodochrosite | Level 12
Hello,
You are modifying the value of i inside the do loop on the different values of i.
sylvainbg
Obsidian | Level 7

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?

gamotte
Rhodochrosite | Level 12

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 ?

Reeza
Super User

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?

LinusH
Tourmaline | Level 20

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
sylvainbg
Obsidian | Level 7

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

Reeza
Super User

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'?

sylvainbg
Obsidian | Level 7

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.

Kurt_Bremser
Super User

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.

sylvainbg
Obsidian | Level 7

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)

Reeza
Super User

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? 

sylvainbg
Obsidian | Level 7

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

Kurt_Bremser
Super User

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.

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
  • 13 replies
  • 1408 views
  • 0 likes
  • 5 in conversation