This solution worked perfectly: libname db odbc noprompt = "server=happp02vw\reporting;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes;DATABASE=AMS" schema = dbo; ... View more

Thank you everyone.   I am all set.   There were only 3 values for flag. Thanks again. ... View more

Thank you so much! ... View more

Sorry about "misspeaking" on my part.   Windows 10 SAS installed on local PC.  I added a "member" and I was able to read it.      The member is in XLS format and not newer EXCEL version (XLSX).   Do I need now to define all columns in XLS file in a datastep? I have been using "Proc Import" after un-compressing the file in the past. Thanks very much! filename in1 ZIP 'C:\health.zip' member="myhealth.xls"; data be; infile in1; input; run;     ... View more

No it is a network drive (client/server) ... View more

data want; set hh.test; array x1{*} $ net1-net7 ; array x2{*} count1-count7; array x3{*} allow1-allow7; array y1{*} $ 80 _net1-_net7 ; array y2{*} _count1-_count7 ; array y3{*} _allow1-_allow7; n1=dim(x1)+1; n2=dim(x2)+1; n3=dim(x3)+1; do i=dim(x1) to 1 by -1; if not missing(x1{i}) then do; n1+(-1);y1{n1}=x1{i}; end; end; do j=dim(x2) to 1 by -1; if not missing(x2{j}) then do; n2+(-1);y2{n2}=x2{j}; end; end; do k=dim(x3) to 1 by -1; if not missing(x3{k}) then do; n3+(-1);y3{n3}=x3{k}; end; end; drop i j k n1 n2 n3 net1-net7 count1-count7 allow1-allow7; run; proc export data=want outfile="c:\want.xlsx" replace dbms=excel; run; ... View more

Hi again, I am sorry to take your time again but your wonderful solution only worked partly and when I applied it to more observation it did not work. Attached are samples with more obs. I appreciate it if you can take a second look. ... View more

Thank for your quick response - but - unfortunately it is not working. This is the output when I ran your code: Obs ID NET1 NET2 NET3 1 HMO POS PPO 2 POS PPO 3 PPO This is what I am trying to get: ID NET1 NET2 NET3 ID1 HMO POS PPO ID2 POS PPO ID3 PPO ... View more