Oleg_L Tracker

transposing data

Oleg_L — Fri, 05 Jul 2019 08:48:49 GMT

Dear All!

I need help. I do not know how to find the solution.

I have table1 and table2 and I need to transpose table1 and table2 to table3

data table1;

infile cards;

informat account $3. date ddmmyy10. sum 8.;

input account--sum;

cards;

A01 30/05/2019 100

A01 20/06/2019 110

A01 27/06/2019 120

A02 30/05/2019 10

A02 20/06/2019 20

A02 27/06/2019 30

;

data table2;

infile cards;

informat account $3. date ddmmyy10. segment $2. koef 8.;

input account--koef;

cards;

A01 30/05/2019 RB 0.26

A01 30/05/2019 KB 0.74

A02 30/05/2019 RB 0.34

A02 30/05/2019 KB 0.66

A01 20/06/2019 RB 0.26

A01 20/06/2019 KB 0.74

A02 20/06/2019 RB 0.34

A02 20/06/2019 KB 0.66

A01 27/06/2019 RB 0.26

A01 27/06/2019 KB 0.74

A02 27/06/2019 RB 0.34

A02 27/06/2019 KB 0.66

;

This is what I need:

data table3;

infile cards;

informat account $3. sum1-sum3 8. koef1-koef3 8. segment1-segment3 $2.;

input account--segment3;

cards;

A01 100 110 120 0.26 0.26 0.26 RB RB RB

A01 100 110 120 0.74 0.74 0.74 KB KB KB

A02 10 20 30 0.34 0.34 0.34 RB RB RB

A02 10 20 30 0.66 0.66 0.66 KB KB KB

;

Thank you

BR Oleg

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 15:19:15 GMT

I changed file extension to txt and opened the result with Excel through text import wizard. Excel opened the file fine this way, while there was a problem to open via text to columns.

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 13:28:55 GMT

That's right.
Thank you.

Oleg

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 13:27:38 GMT

Thanks a lot. You are right.

Oleg

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 12:56:56 GMT

recfm=n does not solves the problem.

Re: How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 12:48:34 GMT

When I view the output csv in a file manager or try open it in excel the recordes are wrapped when them exceed 32767 characters.

That is the problem.

I do not know how to avoid it.

How to export large number of variables to csv without wrapping the lines

Oleg_L — Tue, 25 Jul 2017 12:17:19 GMT

Dear All,

I need to export SAS dataset that exceeds linesize 32767 to csv.

How can I do it?

I use SAS 9.4.

My code is below.

Many thanks,

Oleg

data test;

array MY_VARIABLE_NAME_[1000] $100;

retain MY_VARIABLE_NAME_ '1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111';

output;

run;

proc transpose data=test (obs=0) out=names;

var _all_;

run;

data _null_;

set names ;

file "c:\work\out1.csv" dsd dlm='09'x lrecl=500000 ;

put _name_ @;

run;

data _null_;

set test;

file "c:\work\out1.csv" dsd dlm='09'x lrecl=500000 MOD ;

put (_all_) (:) ;

run;

Re: Need to save excel with password

Oleg_L — Wed, 04 Dec 2013 14:11:41 GMT

Recommend you google search for "macrofun.hlp".

Re: Anyone know how to use the IRR function?

Oleg_L — Tue, 12 Nov 2013 10:47:48 GMT

Note:

I make some experiments calculating IRR in Excel and SAS.

The calculation in Excel and SAS equals if make a daily cash flow both in Excel and SAS. I mean you have 6/30/2013 7/1/2013 7/2/2013 ... and so on 365 points per year most of them are missing or equals zero.

Then you can use IRR=IRR(365, of col:);

Oleg.

Re: Anyone know how to use the IRR function?

Oleg_L — Tue, 12 Nov 2013 10:26:22 GMT

Hi.

Try the code below.

data have;

infile cards;

informat Date mmddyy10. Axiom dollar10.;

input date axiom;

format date ddmmyy10.;

cards;

6/30/2013 ($17,601,671)

9/30/2013 $575,049

12/31/2013 $575,049

3/31/2014 $562,548

6/30/2014 $568,799

9/30/2014 $819,636

12/31/2014 $812,448

3/31/2015 $793,071

6/30/2015 $792,055

9/30/2015 $790,883

12/31/2015 $783,695

3/31/2016 $770,725

6/30/2016 $763,615

8/15/2016 $17,868,981

;

proc transpose data=have out=have1; var axiom; run;

data want; set have1;

irr=irr(4,of col:);

run;

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Oleg_L — Fri, 11 Oct 2013 12:02:56 GMT

Hi.

Your task is not clear to me. May be my English is not good enough.

I hope someone else can help you.

Oleg.

Re: DDE session not ready when using the Excel select function

Oleg_L — Tue, 01 Oct 2013 13:39:44 GMT

I suppose that you have to reassigne filename before using select.

From

filename DDEcmds dde "excel|system";

filename DDEcmds dde "excel|sheet_name_!r3c1:r29C256" notab;

Notice, that you should type real sheet name in the filename statement.

Oleg.

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Oleg_L — Tue, 01 Oct 2013 10:39:28 GMT

You may want to increase the length of variables dd and kp if you have many variables.

See line:

data _null_; length dd $300. kp $300.;

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Oleg_L — Tue, 01 Oct 2013 10:27:35 GMT

var _all_;

run;

data w2; set w1; length name1 $10.;

if substr(_name_,length(_name_),1) in ('1' '2' '3') then do;

name1=substr(_name_,1,length(_name_)-1);

id=input(substr(_name_,length(_name_),1),8.);

end;

else name1=_name_;

run;

filename c temp;

data _null_; length dd $300. kp $300.; retain dd kp;

file c;

set w2 end=eof;; by name1 notsorted ;

if _n_=1 then put 'data want; set have;';

if first.name1 then kp=catx(' ',kp,name1);

if first.name1 and not missing(id) then do;

put 'array _' name1 name1 +(-1)':;';

dd=catt(dd,';',name1,'=_',name1,'(i)');

end;

if eof then do;

put 'do i=1 to 3;';

put dd; put ';output;end;';

put 'keep' +(1) kp ';run;';

end;

run;

%include c /source2;

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Oleg_L — Tue, 01 Oct 2013 08:53:26 GMT

infile cards dlm=',' dsd truncover;

informat Name $10. Firstname $10. Number 8. Hobby1 $10. Hobby2 $10. Hobby3 $10. Adress1 $10. Adress2 $10. Adress3 $10. Tel1 $10. Tel2 $10. Tel3 $10.

Mobil1 $10. Mobil2 $10. Mobil3 $10. X1 $10. X2 $10. X3 $10.;

input Name Firstname Number Hobby1 Hobby2 Hobby3 Adress1 Adress2 Adress3 Tel1 Tel2 Tel3 Mobil1 Mobil2 Mobil3 X1 X2 X3;

cards;

Lee,Mike,1,reading,,,P30,,,1234,,,012345,,,open,,,

Frank,Sue,2,biking,swimming,,B23,P12,,2345,3456,,023456,n.a,,closed,n.a.,,,

Wood,Edward,3,sleeping,reading,travelling,B12,P30,B29,4567,6789,8901,034567,045678,n.a.,closed,n.a.,open,

Mueller,Anna,3,theater,cinema,biking,P35,P25,B24,8765,4562,6351,017263,0354542,036352,open,open,closed,

;

data want;

set have;

array _hobby hobby:; array _adress adress:; array _tel tel:; array _mobil mobil:; array _x x:;

do i=1 to 3; hobby=_hobby(i); adress=_adress(i); tel=_tel(i); mobil=_mobil(i); x=_x(i); output;end;

keep name firstname number hobby adress tel mobil x;

run;

Re: need a macro to read excel tables with similar names in a certain file into sas

Oleg_L — Thu, 26 Sep 2013 10:54:09 GMT

This may be useful:

https://communities.sas.com/message/50737#50737

Re: Data Reading Eror

Oleg_L — Wed, 18 Sep 2013 08:51:20 GMT

I take this solution from the post of DN (Data _null_). See the link above.

Re: Data Reading Eror

Oleg_L — Wed, 18 Sep 2013 08:25:44 GMT

length = end-1-1; -> the length of the string that we are reading into variable name.

Hope this helps.

Oleg.

Re: Data Reading Eror

Oleg_L — Tue, 17 Sep 2013 14:11:10 GMT

length dlm $1;

dlm = ',';

infile cards dsd dlm=dlm;

informat name $30. sal 8. comm 8.;

input @;

call scan(_infile_,-2,end,_n_,',');

length = end-1-1;

input name $varying30. length +1 sal comm;

keep name sal comm;

cards;

venkat srini,1000 ,300

srinivas,kiran,raju,3000,150

;

Re: Data Reading Eror

Oleg_L — Tue, 17 Sep 2013 13:25:24 GMT

This may be useful.

https://communities.sas.com/thread/13128?start=0&tstart=0

Oleg.