sql and data step taking lot of time while running

[aanan1417]

Hi sir,

i ran a program in sql and in data step it take 3 hours to complete .

is their any other way so that it can complete i few minute.

data sas0416.CHDR_202210;
set test1.CHDR;
run;

proc sql;
create table sas0416.CHDR_202210 as
select * from test1.CHDR;
quit;

[andreas_lds]

If you want to copy a dataset, use proc copy. I don't know, of course, if the proc can reduce the runtime to minutes, because i know nothing about the environment you are working in. Please post proc contents of test1.chdr.

[aanan1417]

Data Set NameSAS0416.CHDR_202210Observations28123576
Member Type	DATA	Variables	193
Engine	META	Indexes	0
Created	11/02/2022 19:05:51	Observation Length	1120
Last Modified	11/02/2022 19:05:51	Deleted Observations	0
Protection		Compressed	NO
Data Set Type		Sorted	NO
Label
Data Representation	WINDOWS_64
Encoding	wlatin1 Western (Windows)

Data Set Page Size90112

Engine/Host Dependent Information

Number of Data Set Pages351545First Data Page1Max Obs per Page80Obs in First Data Page60Number of Data Set Repairs0ExtendObsCounterYESFilenameG:\Reinsurancelib\chdr_202210.sas7bdatRelease Created9.0401M6Host CreatedX64_SRV16Owner NameRELIGARE\yadav.rahulFile Size30GBFile Size (bytes)31678513152

53ACCTCCYChar3$3.$3.ACCTCCY

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat	Label

56ACCTMETHChar1$1.$1.ACCTMETH50AGNTCOYChar1$1.$1.AGNTCOY51AGNTNUMChar8$8.$8.AGNTNUM49AGNTPFXChar2$2.$2.AGNTPFX23ANNAMT01Num819.219.2ANNAMT0124ANNAMT02Num819.219.2ANNAMT0225ANNAMT03Num819.219.2ANNAMT0326ANNAMT04Num819.219.2ANNAMT0427ANNAMT05Num819.219.2ANNAMT0528ANNAMT06Num819.219.2ANNAMT06125APLSPFROMNum89.9.APLSPFROM132APLSPTONum89.9.APLSPTO118APLSUPRChar1$1.$1.APLSUPR46ASGNCOYChar1$1.$1.ASGNCOY47ASGNNUMChar8$8.$8.ASGNNUM45ASGNPFXChar2$2.$2.ASGNPFX111BANKACCKEYChar20$20.$20.BANKACCKEY184BANKCODEChar2$2.$2.BANKCODE110BANKKEYChar10$10.$10.BANKKEY174BFCHARGEChar1$1.$1.BFCHARGE105BILLAMT01Num819.219.2BILLAMT01106BILLAMT02Num819.219.2BILLAMT02107BILLAMT03Num819.219.2BILLAMT03108BILLAMT04Num819.219.2BILLAMT0463BILLCDNum89.9.BILLCD58BILLCHNLChar2$2.$2.BILLCHNL101BILLDATE01Num89.9.BILLDATE01102BILLDATE02Num89.9.BILLDATE02103BILLDATE03Num89.9.BILLDATE03104BILLDATE04Num89.9.BILLDATE0461BILLDAYChar2$2.$2.BILLDAY57BILLFREQChar2$2.$2.BILLFREQ62BILLMONTHChar2$2.$2.BILLMONTH126BILLSPFROMNum89.9.BILLSPFROM133BILLSPTONum89.9.BILLSPTO119BILLSUPRChar1$1.$1.BILLSUPR64BTDATENum89.9.BTDATE139CAMPAIGNChar6$6.$6.CAMPAIGN21CCDATENum89.9.CCDATE2CHDRCOYChar1$1.$1.CHDRCOY3CHDRNUMChar8$8.$8.CHDRNUM1CHDRPFXChar2$2.$2.CHDRPFX141CHDRSTCDAChar3$3.$3.CHDRSTCDA142CHDRSTCDBChar3$3.$3.CHDRSTCDB143CHDRSTCDCChar3$3.$3.CHDRSTCDC144CHDRSTCDDChar3$3.$3.CHDRSTCDD145CHDRSTCDEChar3$3.$3.CHDRSTCDE169CHGFLAGChar1$1.$1.CHGFLAG158CLUCOYChar1$1.$1.CLUCOY159CLUNUMChar8$8.$8.CLUNUM157CLUPFXChar2$2.$2.CLUPFX48CNTBRANCHChar2$2.$2.CNTBRANCH52CNTCURRChar3$3.$3.CNTCURR181CNTISSNum89.9.CNTISS182CNTRCVNum89.9.CNTRCV6CNTTYPEChar3$3.$3.CNTTYPE59COLLCHNLChar2$2.$2.COLLCHNL127COMMSPFROMNum89.9.COMMSPFROM134COMMSPTONum89.9.COMMSPTO120COMMSUPRChar1$1.$1.COMMSUPR190COPPNNum813.813.8COPPN189COTYPEChar1$1.$1.COTYPE180COVERNTChar8$8.$8.COVERNT37COWNCOYChar1$1.$1.COWNCOY38COWNNUMChar8$8.$8.COWNNUM36COWNPFXChar2$2.$2.COWNPFX54CRATENum820.920.9CRATE22CRDATENum89.9.CRDATE10CURRFROMNum89.9.CURRFROM11CURRTONum89.9.CURRTO193DATIMENum8DATETIME25.6DATETIME25.6DATIME43DESPCOYChar1$1.$1.DESPCOY44DESPNUMChar8$8.$8.DESPNUM42DESPPFXChar2$2.$2.DESPPFX112DISCODE01Char1$1.$1.DISCODE01113DISCODE02Char1$1.$1.DISCODE02114DISCODE03Char1$1.$1.DISCODE03115DISCODE04Char1$1.$1.DISCODE04175DISHNRCNTNum83.3.DISHNRCNT177DISHNRDTENum89.9.DISHNRDTE185DOCNUMChar8$8.$8.DOCNUM188DTECANNum89.9.DTECAN166DUEFLGChar1$1.$1.DUEFLG109FACTHOUSChar2$2.$2.FACTHOUS161FINCOYChar1$1.$1.FINCOY162FINNUMChar8$8.$8.FINNUM160FINPFXChar2$2.$2.FINPFX116GRUPKEYChar12$12.$12.GRUPKEY77INSTBCHNLChar2$2.$2.INSTBCHNL78INSTCCHNLChar2$2.$2.INSTCCHNL79INSTFREQChar2$2.$2.INSTFREQ75INSTFROMNum89.9.INSTFROM98INSTJCTLChar24$24.$24.INSTJCTL92INSTPAST01Num819.219.2INSTPAST0193INSTPAST02Num819.219.2INSTPAST0294INSTPAST03Num819.219.2INSTPAST0395INSTPAST04Num819.219.2INSTPAST0496INSTPAST05Num819.219.2INSTPAST0597INSTPAST06Num819.219.2INSTPAST0686INSTSTAMT01Num819.219.2INSTSTAMT0187INSTSTAMT02Num819.219.2INSTSTAMT0288INSTSTAMT03Num819.219.2INSTSTAMT0389INSTSTAMT04Num819.219.2INSTSTAMT0490INSTSTAMT05Num819.219.2INSTSTAMT0591INSTSTAMT06Num819.219.2INSTSTAMT0676INSTTONum89.9.INSTTO80INSTTOT01Num819.219.2INSTTOT0181INSTTOT02Num819.219.2INSTTOT0282INSTTOT03Num819.219.2INSTTOT0383INSTTOT04Num819.219.2INSTTOT0484INSTTOT05Num819.219.2INSTTOT0585INSTTOT06Num819.219.2INSTTOT06147JACKETChar8$8.$8.JACKET192JOB_NAMEChar10$10.$10.JOB_NAME167LAPRINDChar1$1.$1.LAPRIND128LAPSSPFROMNum89.9.LAPSSPFROM135LAPSSPTONum89.9.LAPSSPTO121LAPSSUPRChar1$1.$1.LAPSSUPR129MAILSPFROMNum89.9.MAILSPFROM136MAILSPTONum89.9.MAILSPTO122MAILSUPRChar1$1.$1.MAILSUPR60MANDREFChar5$5.$5.MANDREF117MEMBSELChar10$10.$10.MEMBSEL155MPLCOYChar1$1.$1.MPLCOY156MPLNUMChar8$8.$8.MPLNUM154MPLPFXChar2$2.$2.MPLPFX99NOFOUTINSTNum84.4.NOFOUTINST146NOFRISKSNum85.5.NOFRISKS130NOTSSPFROMNum89.9.NOTSSPFROM137NOTSSPTONum89.9.NOTSSPTO123NOTSSUPRChar1$1.$1.NOTSSUPR20OCCDATENum89.9.OCCDATE100OUTSTAMTNum819.219.2OUTSTAMT66PAYFLAGChar1$1.$1.PAYFLAG55PAYPLANChar6$6.$6.PAYPLAN40PAYRCOYChar1$1.$1.PAYRCOY41PAYRNUMChar8$8.$8.PAYRNUM39PAYRPFXChar2$2.$2.PAYRPFX152PDINDChar2$2.$2.PDIND176PDTYPEChar1$1.$1.PDTYPE164POACOYChar1$1.$1.POACOY165POANUMChar8$8.$8.POANUM163POAPFXChar2$2.$2.POAPFX170POLPLNChar10$10.$10.POLPLN13PROCFLAGChar2$2.$2.PROCFLAG14PROCIDChar14$14.$14.PROCID12PROCTRANCDChar4$4.$4.PROCTRANCD148PSTATCODEChar2$2.$2.PSTATCODE150PSTATDATENum89.9.PSTATDATE149PSTATREASNChar2$2.$2.PSTATREASN151PSTATTRANNum86.6.PSTATTRAN65PTDATENum89.9.PTDATE183QUOTENOChar8$8.$8.QUOTENO4RECODEChar2$2.$2.RECODE153REGISTERChar3$3.$3.REGISTER35REPNUMChar25$25.$25.REPNUM34REPTYPEChar2$2.$2.REPTYPE32RNLATTNChar2$2.$2.RNLATTN33RNLDURNNum83.3.RNLDURN30RNLNOTSChar2$2.$2.RNLNOTS31RNLNOTTOChar2$2.$2.RNLNOTTO186RNLSTSChar2$2.$2.RNLSTS29RNLTYPEChar2$2.$2.RNLTYPE131RNWLSPFROMNum89.9.RNWLSPFROM138RNWLSPTONum89.9.RNWLSPTO124RNWLSUPRChar1$1.$1.RNWLSUPR5SERVUNITChar2$2.$2.SERVUNIT69SINSTAMT01Num819.219.2SINSTAMT0170SINSTAMT02Num819.219.2SINSTAMT0271SINSTAMT03Num819.219.2SINSTAMT0372SINSTAMT04Num819.219.2SINSTAMT0473SINSTAMT05Num819.219.2SINSTAMT0574SINSTAMT06Num819.219.2SINSTAMT0667SINSTFROMNum89.9.SINSTFROM68SINSTTONum89.9.SINSTTO168SPECINDChar1$1.$1.SPECIND140SRCEBUSChar2$2.$2.SRCEBUS15STATCODEChar2$2.$2.STATCODE17STATDATENum89.9.STATDATE16STATREASNChar2$2.$2.STATREASN18STATTRANNum86.6.STATTRAN178STMPDTYAMTNum817.217.2STMPDTYAMT179STMPDTYDTENum89.9.STMPDTYDTE187SUSTRCDEChar4$4.$4.SUSTRCDE8TRANIDChar14$14.$14.TRANID19TRANLUSEDNum86.6.TRANLUSED7TRANNONum86.6.TRANNO191USER_PROFILEChar10$10.$10.USER_PROFILE9VALIDFLAGChar1$1.$1.VALIDFLAG171WVFDATNum89.9.WVFDAT173WVFINDChar1$1.$1.WVFIND172WVTDATNum89.9.WVTDAT

[andreas_lds]

The important information is:

File Size30GBFile Size (bytes)31678513152

Copying 30 GB will take some time, but should not take hours. Have you tried proc copy?

[aanan1417]

i will try proc copy.

[aanan1417]

Could  you please share the  code

[Kurt_Bremser]

COPY Procedure 

[Kurt_Bremser]

If G: is a network share, I'm not surprised.

Run your steps with

options fullstimer;

set, so we can see where the time is lost.

[LinusH]

Having data partitioned in 30 GB datasets doesn't seem like a good architecture to me. You should probably look to other option better equipped with this type of data, better suited for data management and query. Within SAS SPDE is slightly better (at least for query).

Also, why do you need to make copies of data sets? Why not use them where they resides?

193 variables sounds like that you probably have quite a lot of redundancy. To make the data set smaller you could try compression or normalizing the data structure.

[Astounding · Accepted solution]

Another way to eliminate this copy step would be to modify the earlier step that creates test1.CHDR. Why not have that earlier step create sas0416.CHDR_202210 instead?