topic Re: I would like read some data in excel, and add some values to one of the variables in SAS Programming

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

Thomas_mp — Thu, 22 May 2025 17:30:21 GMT

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?

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

PaigeMiller — Thu, 22 May 2025 17:44:03 GMT

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.

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

Thomas_mp — Thu, 22 May 2025 18:24:07 GMT

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

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

PaigeMiller — Thu, 22 May 2025 18:45:51 GMT

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

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

Thomas_mp — Thu, 22 May 2025 19:20:41 GMT

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 !!!

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

PaigeMiller — Thu, 22 May 2025 21:28:07 GMT

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.

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

Ksharp — Fri, 23 May 2025 01:50:21 GMT

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

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

Thomas_mp — Mon, 26 May 2025 17:34:54 GMT

Thank you very much for your help !!!