I would like read some data in excel, and add some values to one of the variables

[Thomas_mp]

Hello. I would like to add some values to one of the variables (Va).  I would like to add some code, but the first problem I encounter is how to read the data, given that all the variables (in row B in the attached file) have the same name for different individuals (in row A). Attached is a simplified version of my dataset, what I have in the first 11 rows, and what I would like to have. Could you please help me with this?

[PaigeMiller]

Most of us will not download Excel (or other Microsoft Office) files because they can be security threats. Better to present the data using working SAS data step code (examples and instructions) or the less desirable action is to show us a screen capture of the Excel file using the "Insert Photos" icon (do not attach files)

In general, you can read Excel with PROC IMPORT, or LIBNAME with the Excel engine.

[Thomas_mp]

Thank you Paige.

Attached is the data again.  Please note that the first row is the individual IDs, and the second row is the Vbles, tehy ahve the same name for all the individuals.  This is data, and what I would like to have

[PaigeMiller]

The title of your post indicates you want to add some values. Please explain this in more detail.

[Thomas_mp]

Thank you Paige.

Va for individual 0 has 1 value of 23, but I would like to have 5 values of 23 for Va (because there are 5 values for Vb.  Similarly, Va for individual 1 has 1 value of 27, and I would like to have 3 values of 27 (because there are 3 values for Vb); similarly Va for individual 2 has 1 value equal to 33, but I would like to have  8 values of 33 because there are 8 values of Vb.  The idea is to fill the data in the different columns of Va .  I hope this helps !!!

[PaigeMiller]

I think I get it, but this really is a job that is perfectly and easily done in Excel. It probably could be done in SAS, but it would take a lot more effort.

[Ksharp · Accepted solution]

"Va for individual 1 has 1 value of 27, and I would like to have 3 values of 27"

So where is 24 coming from , I did not see it in your data ?

Here could give you a start.

libname x 'C:\Users\xiakeshan\Documents\Downloads';

proc transpose data=x.delete_data_sas(obs=2) out=temp;
var _all_;
run;
proc sql noprint;
select cat('length temp_',_name_,' $ 80;retain temp_',_name_,'; if _n_>2 then do;if not missing(',_name_,') then temp_',_name_,'=',_name_,';else ',_name_,'=temp_',_name_,';end;') 
into :impute separated by ' '
 from temp
  where lowcase(col2)='va';
quit;

data want;
 set x.delete_data_sas;
 &impute.
 drop temp_:;
run;

[Thomas_mp]

Thank you very much for your help !!!