Adding columns based on condition

[matus2711]

Hi everyone, i am beginner despite of SAS and i would be glad if you help me with this problem.
Imagine you have columns for example:
V1 V2 V3 V4 V5 V6 V7
Now i want to add code which complete this row of columns to V30. But you don't know which column is the last one. So you have to find him and based on this info complete row of columns to V30.
Assume that columns which are already there could contain some data but it's not neccesary.
I hope my description is sufficient. If not i can add some more details. Thank you very much for willingnes and your time.

[PaigeMiller]

So you have to find them ...

Find them how? Yes, please explain in more detail.

[matus2711]

Find that last column, in whole dataset it can be any column, but more often the last one is V13, V14 or V15 for example. So for example you detect that last column in this part of dataset is V13 so you then just add others to number 30. My problem is that i don't know which functions are best for it.

[PaigeMiller]

---

@matus2711 wrote:

Find that last column, in whole dataset it can be any column, but more often the last one is V13, V14 or V15 for example. So for example you detect that last column in this part of dataset is V13 so you then just add others to number 30. My problem is that i don't know which functions are best for it.

---

I'm afraid I can't grasp enough meaning here to write a program. Show us a small example, explain what you have and what you want to have.

[PhilC]

I would need more information, as well.

but to start to answer your questions: 

1) This is not an Enterprise Guide question, it is a SAS programming question.

2) One can add column using this syntax. 

data want;
  set have;
  length v8-v30 8;
run;

that's going to add 23 numeric columns to your data.

3) This code will access the metadata of the dataset "HAVE" and create a table, one row per column.

proc sql;
  select *
    from dictionary.Columns
    where libname="WORK"
      AND memname="HAVE"
  ;
quit;

using this type of query one can "count" how many columns are in a dataset.

[matus2711]

Imagine you have columns, e.g.
V1 V2 V3 V4 V5
1. detect or find number of last column, i don't want hardcode them
2. complete row of columns (V1 V2 V3 V4 V5) to V30
Which type of additional info you need for this?

[PaigeMiller]

---

@matus2711 wrote:
Imagine you have columns, e.g.
V1 V2 V3 V4 V5
1. detect or find number of last column, i don't want hardcode them
2. complete row of columns (V1 V2 V3 V4 V5) to V30
Which type of additional info you need for this?

---

Your examples keep talking about V30, why? What does that have to do with the fact that you have columns V1 V2 V3 V4 V5? 

[matus2711]

Because i want to expand dataset to column V30 or B30 or P30, that doesn't matter. Sorry for inaccurate description.

[PhilC]

OK, question then, if one might imagine the variables B1 through B5. Would one want, then, variables B6 through B30?  That is are you wanting to keep the same prefix?  in your case "V" and in this case "B".

[matus2711]

Exactly i want to keep same prefix. thank you for patience

[Dani_Gor]

Hi,

I'm trying to understand,

you have something like this table:

do you want to add the missing columns (in this example from V13 to V30) to the table?

[matus2711]

Yes exactly but you have to keep assumption that in dataset there are not all the columns same and because of that i can't hardcode them by e.g. alter table command.

[Tom · Accepted solution]

Do not think of it as ADDING variables.  Think of it as assuring that ALL of the variables are defined.  So if you want make sure that the variables V1 to V30 are all defined then just add a statement that defines them.  Like a LENGTH statement.

So if V1 to V30 are supposed to be numeric you would do something like this:

data want ;
  length v1-v30 8;
  set have;
run;

If they are supposed to be character then use $ before the length value in the LENGTH statement.

[matus2711]

Thank you very much. Amazing how simple the solution is 👍

[PhilC]

Now I see. If you care to understand more about this subject in SAS in the
future, a good term to remember is SAS "Program data vector" or PDV.
Which is what I would have spoken to if I saw what you were dealing with in
time. There was a similar question asked about ordering of variables in
the last two weeks.